1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    ensure_list,
  30    seq_get,
  31    split_num_words,
  32    subclasses,
  33)
  34from sqlglot.tokens import Token
  35
  36if t.TYPE_CHECKING:
  37    from sqlglot.dialects.dialect import DialectType
  38
  39E = t.TypeVar("E", bound="Expression")
  40
  41
  42class _Expression(type):
  43    def __new__(cls, clsname, bases, attrs):
  44        klass = super().__new__(cls, clsname, bases, attrs)
  45
  46        # When an Expression class is created, its key is automatically set to be
  47        # the lowercase version of the class' name.
  48        klass.key = clsname.lower()
  49
  50        # This is so that docstrings are not inherited in pdoc
  51        klass.__doc__ = klass.__doc__ or ""
  52
  53        return klass
  54
  55
  56class Expression(metaclass=_Expression):
  57    """
  58    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  59    context, such as its child expressions, their names (arg keys), and whether a given child expression
  60    is optional or not.
  61
  62    Attributes:
  63        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  64            and representing expressions as strings.
  65        arg_types: determines what arguments (child nodes) are supported by an expression. It
  66            maps arg keys to booleans that indicate whether the corresponding args are optional.
  67
  68    Example:
  69        >>> class Foo(Expression):
  70        ...     arg_types = {"this": True, "expression": False}
  71
  72        The above definition informs us that Foo is an Expression that requires an argument called
  73        "this" and may also optionally receive an argument called "expression".
  74
  75    Args:
  76        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  77        parent: a reference to the parent expression (or None, in case of root expressions).
  78        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  79            uses to refer to it.
  80        comments: a list of comments that are associated with a given expression. This is used in
  81            order to preserve comments when transpiling SQL code.
  82        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  83            optimizer, in order to enable some transformations that require type information.
  84    """
  85
  86    key = "expression"
  87    arg_types = {"this": True}
  88    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  89
  90    def __init__(self, **args: t.Any):
  91        self.args: t.Dict[str, t.Any] = args
  92        self.parent: t.Optional[Expression] = None
  93        self.arg_key: t.Optional[str] = None
  94        self.comments: t.Optional[t.List[str]] = None
  95        self._type: t.Optional[DataType] = None
  96        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  97        self._hash: t.Optional[int] = None
  98
  99        for arg_key, value in self.args.items():
 100            self._set_parent(arg_key, value)
 101
 102    def __eq__(self, other) -> bool:
 103        return type(self) is type(other) and hash(self) == hash(other)
 104
 105    @property
 106    def hashable_args(self) -> t.Any:
 107        args = (self.args.get(k) for k in self.arg_types)
 108
 109        return tuple(
 110            (tuple(_norm_arg(a) for a in arg) if arg else None)
 111            if type(arg) is list
 112            else (_norm_arg(arg) if arg is not None and arg is not False else None)
 113            for arg in args
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self):
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self):
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def name(self) -> str:
 200        return self.text("this")
 201
 202    @property
 203    def alias_or_name(self):
 204        return self.alias or self.name
 205
 206    @property
 207    def output_name(self):
 208        """
 209        Name of the output column if this expression is a selection.
 210
 211        If the Expression has no output name, an empty string is returned.
 212
 213        Example:
 214            >>> from sqlglot import parse_one
 215            >>> parse_one("SELECT a").expressions[0].output_name
 216            'a'
 217            >>> parse_one("SELECT b AS c").expressions[0].output_name
 218            'c'
 219            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 220            ''
 221        """
 222        return ""
 223
 224    @property
 225    def type(self) -> t.Optional[DataType]:
 226        return self._type
 227
 228    @type.setter
 229    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 230        if dtype and not isinstance(dtype, DataType):
 231            dtype = DataType.build(dtype)
 232        self._type = dtype  # type: ignore
 233
 234    @property
 235    def meta(self) -> t.Dict[str, t.Any]:
 236        if self._meta is None:
 237            self._meta = {}
 238        return self._meta
 239
 240    def __deepcopy__(self, memo):
 241        copy = self.__class__(**deepcopy(self.args))
 242        if self.comments is not None:
 243            copy.comments = deepcopy(self.comments)
 244
 245        if self._type is not None:
 246            copy._type = self._type.copy()
 247
 248        if self._meta is not None:
 249            copy._meta = deepcopy(self._meta)
 250
 251        return copy
 252
 253    def copy(self):
 254        """
 255        Returns a deep copy of the expression.
 256        """
 257        new = deepcopy(self)
 258        new.parent = self.parent
 259        return new
 260
 261    def append(self, arg_key, value):
 262        """
 263        Appends value to arg_key if it's a list or sets it as a new list.
 264
 265        Args:
 266            arg_key (str): name of the list expression arg
 267            value (Any): value to append to the list
 268        """
 269        if not isinstance(self.args.get(arg_key), list):
 270            self.args[arg_key] = []
 271        self.args[arg_key].append(value)
 272        self._set_parent(arg_key, value)
 273
 274    def set(self, arg_key, value):
 275        """
 276        Sets `arg_key` to `value`.
 277
 278        Args:
 279            arg_key (str): name of the expression arg.
 280            value: value to set the arg to.
 281        """
 282        self.args[arg_key] = value
 283        self._set_parent(arg_key, value)
 284
 285    def _set_parent(self, arg_key, value):
 286        if hasattr(value, "parent"):
 287            value.parent = self
 288            value.arg_key = arg_key
 289        elif type(value) is list:
 290            for v in value:
 291                if hasattr(v, "parent"):
 292                    v.parent = self
 293                    v.arg_key = arg_key
 294
 295    @property
 296    def depth(self):
 297        """
 298        Returns the depth of this tree.
 299        """
 300        if self.parent:
 301            return self.parent.depth + 1
 302        return 0
 303
 304    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 305        """Yields the key and expression for all arguments, exploding list args."""
 306        for k, vs in self.args.items():
 307            if type(vs) is list:
 308                for v in vs:
 309                    if hasattr(v, "parent"):
 310                        yield k, v
 311            else:
 312                if hasattr(vs, "parent"):
 313                    yield k, vs
 314
 315    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 316        """
 317        Returns the first node in this tree which matches at least one of
 318        the specified types.
 319
 320        Args:
 321            expression_types: the expression type(s) to match.
 322
 323        Returns:
 324            The node which matches the criteria or None if no such node was found.
 325        """
 326        return next(self.find_all(*expression_types, bfs=bfs), None)
 327
 328    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 329        """
 330        Returns a generator object which visits all nodes in this tree and only
 331        yields those that match at least one of the specified expression types.
 332
 333        Args:
 334            expression_types: the expression type(s) to match.
 335
 336        Returns:
 337            The generator object.
 338        """
 339        for expression, *_ in self.walk(bfs=bfs):
 340            if isinstance(expression, expression_types):
 341                yield expression
 342
 343    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 344        """
 345        Returns a nearest parent matching expression_types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349
 350        Returns:
 351            The parent node.
 352        """
 353        ancestor = self.parent
 354        while ancestor and not isinstance(ancestor, expression_types):
 355            ancestor = ancestor.parent
 356        return t.cast(E, ancestor)
 357
 358    @property
 359    def parent_select(self):
 360        """
 361        Returns the parent select statement.
 362        """
 363        return self.find_ancestor(Select)
 364
 365    @property
 366    def same_parent(self):
 367        """Returns if the parent is the same class as itself."""
 368        return type(self.parent) is self.__class__
 369
 370    def root(self) -> Expression:
 371        """
 372        Returns the root expression of this tree.
 373        """
 374        expression = self
 375        while expression.parent:
 376            expression = expression.parent
 377        return expression
 378
 379    def walk(self, bfs=True, prune=None):
 380        """
 381        Returns a generator object which visits all nodes in this tree.
 382
 383        Args:
 384            bfs (bool): if set to True the BFS traversal order will be applied,
 385                otherwise the DFS traversal will be used instead.
 386            prune ((node, parent, arg_key) -> bool): callable that returns True if
 387                the generator should stop traversing this branch of the tree.
 388
 389        Returns:
 390            the generator object.
 391        """
 392        if bfs:
 393            yield from self.bfs(prune=prune)
 394        else:
 395            yield from self.dfs(prune=prune)
 396
 397    def dfs(self, parent=None, key=None, prune=None):
 398        """
 399        Returns a generator object which visits all nodes in this tree in
 400        the DFS (Depth-first) order.
 401
 402        Returns:
 403            The generator object.
 404        """
 405        parent = parent or self.parent
 406        yield self, parent, key
 407        if prune and prune(self, parent, key):
 408            return
 409
 410        for k, v in self.iter_expressions():
 411            yield from v.dfs(self, k, prune)
 412
 413    def bfs(self, prune=None):
 414        """
 415        Returns a generator object which visits all nodes in this tree in
 416        the BFS (Breadth-first) order.
 417
 418        Returns:
 419            The generator object.
 420        """
 421        queue = deque([(self, self.parent, None)])
 422
 423        while queue:
 424            item, parent, key = queue.popleft()
 425
 426            yield item, parent, key
 427            if prune and prune(item, parent, key):
 428                continue
 429
 430            for k, v in item.iter_expressions():
 431                queue.append((v, item, k))
 432
 433    def unnest(self):
 434        """
 435        Returns the first non parenthesis child or self.
 436        """
 437        expression = self
 438        while type(expression) is Paren:
 439            expression = expression.this
 440        return expression
 441
 442    def unalias(self):
 443        """
 444        Returns the inner expression if this is an Alias.
 445        """
 446        if isinstance(self, Alias):
 447            return self.this
 448        return self
 449
 450    def unnest_operands(self):
 451        """
 452        Returns unnested operands as a tuple.
 453        """
 454        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 455
 456    def flatten(self, unnest=True):
 457        """
 458        Returns a generator which yields child nodes who's parents are the same class.
 459
 460        A AND B AND C -> [A, B, C]
 461        """
 462        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 463            if not type(node) is self.__class__:
 464                yield node.unnest() if unnest else node
 465
 466    def __str__(self):
 467        return self.sql()
 468
 469    def __repr__(self):
 470        return self._to_s()
 471
 472    def sql(self, dialect: DialectType = None, **opts) -> str:
 473        """
 474        Returns SQL string representation of this tree.
 475
 476        Args:
 477            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 478            opts: other `sqlglot.generator.Generator` options.
 479
 480        Returns:
 481            The SQL string.
 482        """
 483        from sqlglot.dialects import Dialect
 484
 485        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 486
 487    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 488        indent = "" if not level else "\n"
 489        indent += "".join(["  "] * level)
 490        left = f"({self.key.upper()} "
 491
 492        args: t.Dict[str, t.Any] = {
 493            k: ", ".join(
 494                v._to_s(hide_missing=hide_missing, level=level + 1)
 495                if hasattr(v, "_to_s")
 496                else str(v)
 497                for v in ensure_list(vs)
 498                if v is not None
 499            )
 500            for k, vs in self.args.items()
 501        }
 502        args["comments"] = self.comments
 503        args["type"] = self.type
 504        args = {k: v for k, v in args.items() if v or not hide_missing}
 505
 506        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 507        right += ")"
 508
 509        return indent + left + right
 510
 511    def transform(self, fun, *args, copy=True, **kwargs):
 512        """
 513        Recursively visits all tree nodes (excluding already transformed ones)
 514        and applies the given transformation function to each node.
 515
 516        Args:
 517            fun (function): a function which takes a node as an argument and returns a
 518                new transformed node or the same node without modifications. If the function
 519                returns None, then the corresponding node will be removed from the syntax tree.
 520            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 521                modified in place.
 522
 523        Returns:
 524            The transformed tree.
 525        """
 526        node = self.copy() if copy else self
 527        new_node = fun(node, *args, **kwargs)
 528
 529        if new_node is None or not isinstance(new_node, Expression):
 530            return new_node
 531        if new_node is not node:
 532            new_node.parent = node.parent
 533            return new_node
 534
 535        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 536        return new_node
 537
 538    def replace(self, expression):
 539        """
 540        Swap out this expression with a new expression.
 541
 542        For example::
 543
 544            >>> tree = Select().select("x").from_("tbl")
 545            >>> tree.find(Column).replace(Column(this="y"))
 546            (COLUMN this: y)
 547            >>> tree.sql()
 548            'SELECT y FROM tbl'
 549
 550        Args:
 551            expression (Expression|None): new node
 552
 553        Returns:
 554            The new expression or expressions.
 555        """
 556        if not self.parent:
 557            return expression
 558
 559        parent = self.parent
 560        self.parent = None
 561
 562        replace_children(parent, lambda child: expression if child is self else child)
 563        return expression
 564
 565    def pop(self):
 566        """
 567        Remove this expression from its AST.
 568
 569        Returns:
 570            The popped expression.
 571        """
 572        self.replace(None)
 573        return self
 574
 575    def assert_is(self, type_):
 576        """
 577        Assert that this `Expression` is an instance of `type_`.
 578
 579        If it is NOT an instance of `type_`, this raises an assertion error.
 580        Otherwise, this returns this expression.
 581
 582        Examples:
 583            This is useful for type security in chained expressions:
 584
 585            >>> import sqlglot
 586            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 587            'SELECT x, z FROM y'
 588        """
 589        assert isinstance(self, type_)
 590        return self
 591
 592    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 593        """
 594        Checks if this expression is valid (e.g. all mandatory args are set).
 595
 596        Args:
 597            args: a sequence of values that were used to instantiate a Func expression. This is used
 598                to check that the provided arguments don't exceed the function argument limit.
 599
 600        Returns:
 601            A list of error messages for all possible errors that were found.
 602        """
 603        errors: t.List[str] = []
 604
 605        for k in self.args:
 606            if k not in self.arg_types:
 607                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 608        for k, mandatory in self.arg_types.items():
 609            v = self.args.get(k)
 610            if mandatory and (v is None or (isinstance(v, list) and not v)):
 611                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 612
 613        if (
 614            args
 615            and isinstance(self, Func)
 616            and len(args) > len(self.arg_types)
 617            and not self.is_var_len_args
 618        ):
 619            errors.append(
 620                f"The number of provided arguments ({len(args)}) is greater than "
 621                f"the maximum number of supported arguments ({len(self.arg_types)})"
 622            )
 623
 624        return errors
 625
 626    def dump(self):
 627        """
 628        Dump this Expression to a JSON-serializable dict.
 629        """
 630        from sqlglot.serde import dump
 631
 632        return dump(self)
 633
 634    @classmethod
 635    def load(cls, obj):
 636        """
 637        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 638        """
 639        from sqlglot.serde import load
 640
 641        return load(obj)
 642
 643
 644IntoType = t.Union[
 645    str,
 646    t.Type[Expression],
 647    t.Collection[t.Union[str, t.Type[Expression]]],
 648]
 649ExpOrStr = t.Union[str, Expression]
 650
 651
 652class Condition(Expression):
 653    def and_(self, *expressions, dialect=None, **opts):
 654        """
 655        AND this condition with one or multiple expressions.
 656
 657        Example:
 658            >>> condition("x=1").and_("y=1").sql()
 659            'x = 1 AND y = 1'
 660
 661        Args:
 662            *expressions (str | Expression): the SQL code strings to parse.
 663                If an `Expression` instance is passed, it will be used as-is.
 664            dialect (str): the dialect used to parse the input expression.
 665            opts (kwargs): other options to use to parse the input expressions.
 666
 667        Returns:
 668            And: the new condition.
 669        """
 670        return and_(self, *expressions, dialect=dialect, **opts)
 671
 672    def or_(self, *expressions, dialect=None, **opts):
 673        """
 674        OR this condition with one or multiple expressions.
 675
 676        Example:
 677            >>> condition("x=1").or_("y=1").sql()
 678            'x = 1 OR y = 1'
 679
 680        Args:
 681            *expressions (str | Expression): the SQL code strings to parse.
 682                If an `Expression` instance is passed, it will be used as-is.
 683            dialect (str): the dialect used to parse the input expression.
 684            opts (kwargs): other options to use to parse the input expressions.
 685
 686        Returns:
 687            Or: the new condition.
 688        """
 689        return or_(self, *expressions, dialect=dialect, **opts)
 690
 691    def not_(self):
 692        """
 693        Wrap this condition with NOT.
 694
 695        Example:
 696            >>> condition("x=1").not_().sql()
 697            'NOT x = 1'
 698
 699        Returns:
 700            Not: the new condition.
 701        """
 702        return not_(self)
 703
 704
 705class Predicate(Condition):
 706    """Relationships like x = y, x > 1, x >= y."""
 707
 708
 709class DerivedTable(Expression):
 710    @property
 711    def alias_column_names(self):
 712        table_alias = self.args.get("alias")
 713        if not table_alias:
 714            return []
 715        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 716        return [c.name for c in column_list]
 717
 718    @property
 719    def selects(self):
 720        alias = self.args.get("alias")
 721
 722        if alias:
 723            return alias.columns
 724        return []
 725
 726    @property
 727    def named_selects(self):
 728        return [select.output_name for select in self.selects]
 729
 730
 731class Unionable(Expression):
 732    def union(self, expression, distinct=True, dialect=None, **opts):
 733        """
 734        Builds a UNION expression.
 735
 736        Example:
 737            >>> import sqlglot
 738            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 739            'SELECT * FROM foo UNION SELECT * FROM bla'
 740
 741        Args:
 742            expression (str | Expression): the SQL code string.
 743                If an `Expression` instance is passed, it will be used as-is.
 744            distinct (bool): set the DISTINCT flag if and only if this is true.
 745            dialect (str): the dialect used to parse the input expression.
 746            opts (kwargs): other options to use to parse the input expressions.
 747        Returns:
 748            Union: the Union expression.
 749        """
 750        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 751
 752    def intersect(self, expression, distinct=True, dialect=None, **opts):
 753        """
 754        Builds an INTERSECT expression.
 755
 756        Example:
 757            >>> import sqlglot
 758            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 759            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 760
 761        Args:
 762            expression (str | Expression): the SQL code string.
 763                If an `Expression` instance is passed, it will be used as-is.
 764            distinct (bool): set the DISTINCT flag if and only if this is true.
 765            dialect (str): the dialect used to parse the input expression.
 766            opts (kwargs): other options to use to parse the input expressions.
 767        Returns:
 768            Intersect: the Intersect expression
 769        """
 770        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 771
 772    def except_(self, expression, distinct=True, dialect=None, **opts):
 773        """
 774        Builds an EXCEPT expression.
 775
 776        Example:
 777            >>> import sqlglot
 778            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 779            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 780
 781        Args:
 782            expression (str | Expression): the SQL code string.
 783                If an `Expression` instance is passed, it will be used as-is.
 784            distinct (bool): set the DISTINCT flag if and only if this is true.
 785            dialect (str): the dialect used to parse the input expression.
 786            opts (kwargs): other options to use to parse the input expressions.
 787        Returns:
 788            Except: the Except expression
 789        """
 790        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 791
 792
 793class UDTF(DerivedTable, Unionable):
 794    pass
 795
 796
 797class Cache(Expression):
 798    arg_types = {
 799        "with": False,
 800        "this": True,
 801        "lazy": False,
 802        "options": False,
 803        "expression": False,
 804    }
 805
 806
 807class Uncache(Expression):
 808    arg_types = {"this": True, "exists": False}
 809
 810
 811class Create(Expression):
 812    arg_types = {
 813        "with": False,
 814        "this": True,
 815        "kind": True,
 816        "expression": False,
 817        "exists": False,
 818        "properties": False,
 819        "replace": False,
 820        "unique": False,
 821        "indexes": False,
 822        "no_schema_binding": False,
 823        "begin": False,
 824    }
 825
 826
 827class Describe(Expression):
 828    arg_types = {"this": True, "kind": False}
 829
 830
 831class Pragma(Expression):
 832    pass
 833
 834
 835class Set(Expression):
 836    arg_types = {"expressions": False}
 837
 838
 839class SetItem(Expression):
 840    arg_types = {
 841        "this": False,
 842        "expressions": False,
 843        "kind": False,
 844        "collate": False,  # MySQL SET NAMES statement
 845        "global": False,
 846    }
 847
 848
 849class Show(Expression):
 850    arg_types = {
 851        "this": True,
 852        "target": False,
 853        "offset": False,
 854        "limit": False,
 855        "like": False,
 856        "where": False,
 857        "db": False,
 858        "full": False,
 859        "mutex": False,
 860        "query": False,
 861        "channel": False,
 862        "global": False,
 863        "log": False,
 864        "position": False,
 865        "types": False,
 866    }
 867
 868
 869class UserDefinedFunction(Expression):
 870    arg_types = {"this": True, "expressions": False, "wrapped": False}
 871
 872
 873class CharacterSet(Expression):
 874    arg_types = {"this": True, "default": False}
 875
 876
 877class With(Expression):
 878    arg_types = {"expressions": True, "recursive": False}
 879
 880    @property
 881    def recursive(self) -> bool:
 882        return bool(self.args.get("recursive"))
 883
 884
 885class WithinGroup(Expression):
 886    arg_types = {"this": True, "expression": False}
 887
 888
 889class CTE(DerivedTable):
 890    arg_types = {"this": True, "alias": True}
 891
 892
 893class TableAlias(Expression):
 894    arg_types = {"this": False, "columns": False}
 895
 896    @property
 897    def columns(self):
 898        return self.args.get("columns") or []
 899
 900
 901class BitString(Condition):
 902    pass
 903
 904
 905class HexString(Condition):
 906    pass
 907
 908
 909class ByteString(Condition):
 910    pass
 911
 912
 913class Column(Condition):
 914    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
 915
 916    @property
 917    def table(self) -> str:
 918        return self.text("table")
 919
 920    @property
 921    def db(self) -> str:
 922        return self.text("db")
 923
 924    @property
 925    def catalog(self) -> str:
 926        return self.text("catalog")
 927
 928    @property
 929    def output_name(self) -> str:
 930        return self.name
 931
 932    @property
 933    def parts(self) -> t.List[Identifier]:
 934        """Return the parts of a column in order catalog, db, table, name."""
 935        return [part for part in reversed(list(self.args.values())) if part]
 936
 937    def to_dot(self) -> Dot:
 938        """Converts the column into a dot expression."""
 939        parts = self.parts
 940        parent = self.parent
 941
 942        while parent:
 943            if isinstance(parent, Dot):
 944                parts.append(parent.expression)
 945            parent = parent.parent
 946
 947        return Dot.build(parts)
 948
 949
 950class ColumnPosition(Expression):
 951    arg_types = {"this": False, "position": True}
 952
 953
 954class ColumnDef(Expression):
 955    arg_types = {
 956        "this": True,
 957        "kind": False,
 958        "constraints": False,
 959        "exists": False,
 960        "position": False,
 961    }
 962
 963
 964class AlterColumn(Expression):
 965    arg_types = {
 966        "this": True,
 967        "dtype": False,
 968        "collate": False,
 969        "using": False,
 970        "default": False,
 971        "drop": False,
 972    }
 973
 974
 975class RenameTable(Expression):
 976    pass
 977
 978
 979class SetTag(Expression):
 980    arg_types = {"expressions": True, "unset": False}
 981
 982
 983class Comment(Expression):
 984    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
 985
 986
 987class ColumnConstraint(Expression):
 988    arg_types = {"this": False, "kind": True}
 989
 990
 991class ColumnConstraintKind(Expression):
 992    pass
 993
 994
 995class AutoIncrementColumnConstraint(ColumnConstraintKind):
 996    pass
 997
 998
 999class CaseSpecificColumnConstraint(ColumnConstraintKind):
1000    arg_types = {"not_": True}
1001
1002
1003class CharacterSetColumnConstraint(ColumnConstraintKind):
1004    arg_types = {"this": True}
1005
1006
1007class CheckColumnConstraint(ColumnConstraintKind):
1008    pass
1009
1010
1011class CollateColumnConstraint(ColumnConstraintKind):
1012    pass
1013
1014
1015class CommentColumnConstraint(ColumnConstraintKind):
1016    pass
1017
1018
1019class CompressColumnConstraint(ColumnConstraintKind):
1020    pass
1021
1022
1023class DateFormatColumnConstraint(ColumnConstraintKind):
1024    arg_types = {"this": True}
1025
1026
1027class DefaultColumnConstraint(ColumnConstraintKind):
1028    pass
1029
1030
1031class EncodeColumnConstraint(ColumnConstraintKind):
1032    pass
1033
1034
1035class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1036    # this: True -> ALWAYS, this: False -> BY DEFAULT
1037    arg_types = {
1038        "this": False,
1039        "start": False,
1040        "increment": False,
1041        "minvalue": False,
1042        "maxvalue": False,
1043        "cycle": False,
1044    }
1045
1046
1047class InlineLengthColumnConstraint(ColumnConstraintKind):
1048    pass
1049
1050
1051class NotNullColumnConstraint(ColumnConstraintKind):
1052    arg_types = {"allow_null": False}
1053
1054
1055# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1056class OnUpdateColumnConstraint(ColumnConstraintKind):
1057    pass
1058
1059
1060class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1061    arg_types = {"desc": False}
1062
1063
1064class TitleColumnConstraint(ColumnConstraintKind):
1065    pass
1066
1067
1068class UniqueColumnConstraint(ColumnConstraintKind):
1069    arg_types: t.Dict[str, t.Any] = {}
1070
1071
1072class UppercaseColumnConstraint(ColumnConstraintKind):
1073    arg_types: t.Dict[str, t.Any] = {}
1074
1075
1076class PathColumnConstraint(ColumnConstraintKind):
1077    pass
1078
1079
1080class Constraint(Expression):
1081    arg_types = {"this": True, "expressions": True}
1082
1083
1084class Delete(Expression):
1085    arg_types = {"with": False, "this": False, "using": False, "where": False, "returning": False}
1086
1087    def delete(
1088        self,
1089        table: ExpOrStr,
1090        dialect: DialectType = None,
1091        copy: bool = True,
1092        **opts,
1093    ) -> Delete:
1094        """
1095        Create a DELETE expression or replace the table on an existing DELETE expression.
1096
1097        Example:
1098            >>> delete("tbl").sql()
1099            'DELETE FROM tbl'
1100
1101        Args:
1102            table: the table from which to delete.
1103            dialect: the dialect used to parse the input expression.
1104            copy: if `False`, modify this expression instance in-place.
1105            opts: other options to use to parse the input expressions.
1106
1107        Returns:
1108            Delete: the modified expression.
1109        """
1110        return _apply_builder(
1111            expression=table,
1112            instance=self,
1113            arg="this",
1114            dialect=dialect,
1115            into=Table,
1116            copy=copy,
1117            **opts,
1118        )
1119
1120    def where(
1121        self,
1122        *expressions: ExpOrStr,
1123        append: bool = True,
1124        dialect: DialectType = None,
1125        copy: bool = True,
1126        **opts,
1127    ) -> Delete:
1128        """
1129        Append to or set the WHERE expressions.
1130
1131        Example:
1132            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1133            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1134
1135        Args:
1136            *expressions: the SQL code strings to parse.
1137                If an `Expression` instance is passed, it will be used as-is.
1138                Multiple expressions are combined with an AND operator.
1139            append: if `True`, AND the new expressions to any existing expression.
1140                Otherwise, this resets the expression.
1141            dialect: the dialect used to parse the input expressions.
1142            copy: if `False`, modify this expression instance in-place.
1143            opts: other options to use to parse the input expressions.
1144
1145        Returns:
1146            Delete: the modified expression.
1147        """
1148        return _apply_conjunction_builder(
1149            *expressions,
1150            instance=self,
1151            arg="where",
1152            append=append,
1153            into=Where,
1154            dialect=dialect,
1155            copy=copy,
1156            **opts,
1157        )
1158
1159    def returning(
1160        self,
1161        expression: ExpOrStr,
1162        dialect: DialectType = None,
1163        copy: bool = True,
1164        **opts,
1165    ) -> Delete:
1166        """
1167        Set the RETURNING expression. Not supported by all dialects.
1168
1169        Example:
1170            >>> delete("tbl").returning("*", dialect="postgres").sql()
1171            'DELETE FROM tbl RETURNING *'
1172
1173        Args:
1174            expression: the SQL code strings to parse.
1175                If an `Expression` instance is passed, it will be used as-is.
1176            dialect: the dialect used to parse the input expressions.
1177            copy: if `False`, modify this expression instance in-place.
1178            opts: other options to use to parse the input expressions.
1179
1180        Returns:
1181            Delete: the modified expression.
1182        """
1183        return _apply_builder(
1184            expression=expression,
1185            instance=self,
1186            arg="returning",
1187            prefix="RETURNING",
1188            dialect=dialect,
1189            copy=copy,
1190            into=Returning,
1191            **opts,
1192        )
1193
1194
1195class Drop(Expression):
1196    arg_types = {
1197        "this": False,
1198        "kind": False,
1199        "exists": False,
1200        "temporary": False,
1201        "materialized": False,
1202        "cascade": False,
1203        "constraints": False,
1204        "purge": False,
1205    }
1206
1207
1208class Filter(Expression):
1209    arg_types = {"this": True, "expression": True}
1210
1211
1212class Check(Expression):
1213    pass
1214
1215
1216class Directory(Expression):
1217    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1218    arg_types = {"this": True, "local": False, "row_format": False}
1219
1220
1221class ForeignKey(Expression):
1222    arg_types = {
1223        "expressions": True,
1224        "reference": False,
1225        "delete": False,
1226        "update": False,
1227    }
1228
1229
1230class PrimaryKey(Expression):
1231    arg_types = {"expressions": True, "options": False}
1232
1233
1234class Unique(Expression):
1235    arg_types = {"expressions": True}
1236
1237
1238# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1239# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1240class Into(Expression):
1241    arg_types = {"this": True, "temporary": False, "unlogged": False}
1242
1243
1244class From(Expression):
1245    arg_types = {"expressions": True}
1246
1247
1248class Having(Expression):
1249    pass
1250
1251
1252class Hint(Expression):
1253    arg_types = {"expressions": True}
1254
1255
1256class JoinHint(Expression):
1257    arg_types = {"this": True, "expressions": True}
1258
1259
1260class Identifier(Expression):
1261    arg_types = {"this": True, "quoted": False}
1262
1263    @property
1264    def quoted(self):
1265        return bool(self.args.get("quoted"))
1266
1267    @property
1268    def hashable_args(self) -> t.Any:
1269        if self.quoted and any(char.isupper() for char in self.this):
1270            return (self.this, self.quoted)
1271        return self.this.lower()
1272
1273    @property
1274    def output_name(self):
1275        return self.name
1276
1277
1278class Index(Expression):
1279    arg_types = {
1280        "this": False,
1281        "table": False,
1282        "where": False,
1283        "columns": False,
1284        "unique": False,
1285        "primary": False,
1286        "amp": False,  # teradata
1287    }
1288
1289
1290class Insert(Expression):
1291    arg_types = {
1292        "with": False,
1293        "this": True,
1294        "expression": False,
1295        "returning": False,
1296        "overwrite": False,
1297        "exists": False,
1298        "partition": False,
1299        "alternative": False,
1300    }
1301
1302
1303class Returning(Expression):
1304    arg_types = {"expressions": True}
1305
1306
1307# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1308class Introducer(Expression):
1309    arg_types = {"this": True, "expression": True}
1310
1311
1312# national char, like n'utf8'
1313class National(Expression):
1314    pass
1315
1316
1317class LoadData(Expression):
1318    arg_types = {
1319        "this": True,
1320        "local": False,
1321        "overwrite": False,
1322        "inpath": True,
1323        "partition": False,
1324        "input_format": False,
1325        "serde": False,
1326    }
1327
1328
1329class Partition(Expression):
1330    arg_types = {"expressions": True}
1331
1332
1333class Fetch(Expression):
1334    arg_types = {"direction": False, "count": False}
1335
1336
1337class Group(Expression):
1338    arg_types = {
1339        "expressions": False,
1340        "grouping_sets": False,
1341        "cube": False,
1342        "rollup": False,
1343    }
1344
1345
1346class Lambda(Expression):
1347    arg_types = {"this": True, "expressions": True}
1348
1349
1350class Limit(Expression):
1351    arg_types = {"this": False, "expression": True}
1352
1353
1354class Literal(Condition):
1355    arg_types = {"this": True, "is_string": True}
1356
1357    @property
1358    def hashable_args(self) -> t.Any:
1359        return (self.this, self.args.get("is_string"))
1360
1361    @classmethod
1362    def number(cls, number) -> Literal:
1363        return cls(this=str(number), is_string=False)
1364
1365    @classmethod
1366    def string(cls, string) -> Literal:
1367        return cls(this=str(string), is_string=True)
1368
1369    @property
1370    def output_name(self):
1371        return self.name
1372
1373
1374class Join(Expression):
1375    arg_types = {
1376        "this": True,
1377        "on": False,
1378        "side": False,
1379        "kind": False,
1380        "using": False,
1381        "natural": False,
1382    }
1383
1384    @property
1385    def kind(self):
1386        return self.text("kind").upper()
1387
1388    @property
1389    def side(self):
1390        return self.text("side").upper()
1391
1392    @property
1393    def alias_or_name(self):
1394        return self.this.alias_or_name
1395
1396    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1397        """
1398        Append to or set the ON expressions.
1399
1400        Example:
1401            >>> import sqlglot
1402            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1403            'JOIN x ON y = 1'
1404
1405        Args:
1406            *expressions (str | Expression): the SQL code strings to parse.
1407                If an `Expression` instance is passed, it will be used as-is.
1408                Multiple expressions are combined with an AND operator.
1409            append (bool): if `True`, AND the new expressions to any existing expression.
1410                Otherwise, this resets the expression.
1411            dialect (str): the dialect used to parse the input expressions.
1412            copy (bool): if `False`, modify this expression instance in-place.
1413            opts (kwargs): other options to use to parse the input expressions.
1414
1415        Returns:
1416            Join: the modified join expression.
1417        """
1418        join = _apply_conjunction_builder(
1419            *expressions,
1420            instance=self,
1421            arg="on",
1422            append=append,
1423            dialect=dialect,
1424            copy=copy,
1425            **opts,
1426        )
1427
1428        if join.kind == "CROSS":
1429            join.set("kind", None)
1430
1431        return join
1432
1433    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1434        """
1435        Append to or set the USING expressions.
1436
1437        Example:
1438            >>> import sqlglot
1439            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1440            'JOIN x USING (foo, bla)'
1441
1442        Args:
1443            *expressions (str | Expression): the SQL code strings to parse.
1444                If an `Expression` instance is passed, it will be used as-is.
1445            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1446                Otherwise, this resets the expression.
1447            dialect (str): the dialect used to parse the input expressions.
1448            copy (bool): if `False`, modify this expression instance in-place.
1449            opts (kwargs): other options to use to parse the input expressions.
1450
1451        Returns:
1452            Join: the modified join expression.
1453        """
1454        join = _apply_list_builder(
1455            *expressions,
1456            instance=self,
1457            arg="using",
1458            append=append,
1459            dialect=dialect,
1460            copy=copy,
1461            **opts,
1462        )
1463
1464        if join.kind == "CROSS":
1465            join.set("kind", None)
1466
1467        return join
1468
1469
1470class Lateral(UDTF):
1471    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1472
1473
1474class MatchRecognize(Expression):
1475    arg_types = {
1476        "partition_by": False,
1477        "order": False,
1478        "measures": False,
1479        "rows": False,
1480        "after": False,
1481        "pattern": False,
1482        "define": False,
1483        "alias": False,
1484    }
1485
1486
1487# Clickhouse FROM FINAL modifier
1488# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1489class Final(Expression):
1490    pass
1491
1492
1493class Offset(Expression):
1494    arg_types = {"this": False, "expression": True}
1495
1496
1497class Order(Expression):
1498    arg_types = {"this": False, "expressions": True}
1499
1500
1501# hive specific sorts
1502# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1503class Cluster(Order):
1504    pass
1505
1506
1507class Distribute(Order):
1508    pass
1509
1510
1511class Sort(Order):
1512    pass
1513
1514
1515class Ordered(Expression):
1516    arg_types = {"this": True, "desc": True, "nulls_first": True}
1517
1518
1519class Property(Expression):
1520    arg_types = {"this": True, "value": True}
1521
1522
1523class AfterJournalProperty(Property):
1524    arg_types = {"no": True, "dual": False, "local": False}
1525
1526
1527class AlgorithmProperty(Property):
1528    arg_types = {"this": True}
1529
1530
1531class AutoIncrementProperty(Property):
1532    arg_types = {"this": True}
1533
1534
1535class BlockCompressionProperty(Property):
1536    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1537
1538
1539class CharacterSetProperty(Property):
1540    arg_types = {"this": True, "default": True}
1541
1542
1543class ChecksumProperty(Property):
1544    arg_types = {"on": False, "default": False}
1545
1546
1547class CollateProperty(Property):
1548    arg_types = {"this": True}
1549
1550
1551class DataBlocksizeProperty(Property):
1552    arg_types = {"size": False, "units": False, "min": False, "default": False}
1553
1554
1555class DefinerProperty(Property):
1556    arg_types = {"this": True}
1557
1558
1559class DistKeyProperty(Property):
1560    arg_types = {"this": True}
1561
1562
1563class DistStyleProperty(Property):
1564    arg_types = {"this": True}
1565
1566
1567class EngineProperty(Property):
1568    arg_types = {"this": True}
1569
1570
1571class ExecuteAsProperty(Property):
1572    arg_types = {"this": True}
1573
1574
1575class ExternalProperty(Property):
1576    arg_types = {"this": False}
1577
1578
1579class FallbackProperty(Property):
1580    arg_types = {"no": True, "protection": False}
1581
1582
1583class FileFormatProperty(Property):
1584    arg_types = {"this": True}
1585
1586
1587class FreespaceProperty(Property):
1588    arg_types = {"this": True, "percent": False}
1589
1590
1591class InputOutputFormat(Expression):
1592    arg_types = {"input_format": False, "output_format": False}
1593
1594
1595class IsolatedLoadingProperty(Property):
1596    arg_types = {
1597        "no": True,
1598        "concurrent": True,
1599        "for_all": True,
1600        "for_insert": True,
1601        "for_none": True,
1602    }
1603
1604
1605class JournalProperty(Property):
1606    arg_types = {"no": True, "dual": False, "before": False}
1607
1608
1609class LanguageProperty(Property):
1610    arg_types = {"this": True}
1611
1612
1613class LikeProperty(Property):
1614    arg_types = {"this": True, "expressions": False}
1615
1616
1617class LocationProperty(Property):
1618    arg_types = {"this": True}
1619
1620
1621class LockingProperty(Property):
1622    arg_types = {
1623        "this": False,
1624        "kind": True,
1625        "for_or_in": True,
1626        "lock_type": True,
1627        "override": False,
1628    }
1629
1630
1631class LogProperty(Property):
1632    arg_types = {"no": True}
1633
1634
1635class MaterializedProperty(Property):
1636    arg_types = {"this": False}
1637
1638
1639class MergeBlockRatioProperty(Property):
1640    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1641
1642
1643class NoPrimaryIndexProperty(Property):
1644    arg_types = {"this": False}
1645
1646
1647class OnCommitProperty(Property):
1648    arg_type = {"this": False}
1649
1650
1651class PartitionedByProperty(Property):
1652    arg_types = {"this": True}
1653
1654
1655class ReturnsProperty(Property):
1656    arg_types = {"this": True, "is_table": False, "table": False}
1657
1658
1659class RowFormatProperty(Property):
1660    arg_types = {"this": True}
1661
1662
1663class RowFormatDelimitedProperty(Property):
1664    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1665    arg_types = {
1666        "fields": False,
1667        "escaped": False,
1668        "collection_items": False,
1669        "map_keys": False,
1670        "lines": False,
1671        "null": False,
1672        "serde": False,
1673    }
1674
1675
1676class RowFormatSerdeProperty(Property):
1677    arg_types = {"this": True}
1678
1679
1680class SchemaCommentProperty(Property):
1681    arg_types = {"this": True}
1682
1683
1684class SerdeProperties(Property):
1685    arg_types = {"expressions": True}
1686
1687
1688class SetProperty(Property):
1689    arg_types = {"multi": True}
1690
1691
1692class SortKeyProperty(Property):
1693    arg_types = {"this": True, "compound": False}
1694
1695
1696class SqlSecurityProperty(Property):
1697    arg_types = {"definer": True}
1698
1699
1700class StabilityProperty(Property):
1701    arg_types = {"this": True}
1702
1703
1704class TableFormatProperty(Property):
1705    arg_types = {"this": True}
1706
1707
1708class TemporaryProperty(Property):
1709    arg_types = {"global_": True}
1710
1711
1712class TransientProperty(Property):
1713    arg_types = {"this": False}
1714
1715
1716class VolatileProperty(Property):
1717    arg_types = {"this": False}
1718
1719
1720class WithDataProperty(Property):
1721    arg_types = {"no": True, "statistics": False}
1722
1723
1724class WithJournalTableProperty(Property):
1725    arg_types = {"this": True}
1726
1727
1728class Properties(Expression):
1729    arg_types = {"expressions": True}
1730
1731    NAME_TO_PROPERTY = {
1732        "ALGORITHM": AlgorithmProperty,
1733        "AUTO_INCREMENT": AutoIncrementProperty,
1734        "CHARACTER SET": CharacterSetProperty,
1735        "COLLATE": CollateProperty,
1736        "COMMENT": SchemaCommentProperty,
1737        "DEFINER": DefinerProperty,
1738        "DISTKEY": DistKeyProperty,
1739        "DISTSTYLE": DistStyleProperty,
1740        "ENGINE": EngineProperty,
1741        "EXECUTE AS": ExecuteAsProperty,
1742        "FORMAT": FileFormatProperty,
1743        "LANGUAGE": LanguageProperty,
1744        "LOCATION": LocationProperty,
1745        "PARTITIONED_BY": PartitionedByProperty,
1746        "RETURNS": ReturnsProperty,
1747        "ROW_FORMAT": RowFormatProperty,
1748        "SORTKEY": SortKeyProperty,
1749        "TABLE_FORMAT": TableFormatProperty,
1750    }
1751
1752    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1753
1754    # CREATE property locations
1755    # Form: schema specified
1756    #   create [POST_CREATE]
1757    #     table a [POST_NAME]
1758    #     (b int) [POST_SCHEMA]
1759    #     with ([POST_WITH])
1760    #     index (b) [POST_INDEX]
1761    #
1762    # Form: alias selection
1763    #   create [POST_CREATE]
1764    #     table a [POST_NAME]
1765    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1766    #     index (c) [POST_INDEX]
1767    class Location(AutoName):
1768        POST_CREATE = auto()
1769        POST_NAME = auto()
1770        POST_SCHEMA = auto()
1771        POST_WITH = auto()
1772        POST_ALIAS = auto()
1773        POST_EXPRESSION = auto()
1774        POST_INDEX = auto()
1775        UNSUPPORTED = auto()
1776
1777    @classmethod
1778    def from_dict(cls, properties_dict) -> Properties:
1779        expressions = []
1780        for key, value in properties_dict.items():
1781            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1782            if property_cls:
1783                expressions.append(property_cls(this=convert(value)))
1784            else:
1785                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1786
1787        return cls(expressions=expressions)
1788
1789
1790class Qualify(Expression):
1791    pass
1792
1793
1794# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1795class Return(Expression):
1796    pass
1797
1798
1799class Reference(Expression):
1800    arg_types = {"this": True, "expressions": False, "options": False}
1801
1802
1803class Tuple(Expression):
1804    arg_types = {"expressions": False}
1805
1806
1807class Subqueryable(Unionable):
1808    def subquery(self, alias=None, copy=True) -> Subquery:
1809        """
1810        Convert this expression to an aliased expression that can be used as a Subquery.
1811
1812        Example:
1813            >>> subquery = Select().select("x").from_("tbl").subquery()
1814            >>> Select().select("x").from_(subquery).sql()
1815            'SELECT x FROM (SELECT x FROM tbl)'
1816
1817        Args:
1818            alias (str | Identifier): an optional alias for the subquery
1819            copy (bool): if `False`, modify this expression instance in-place.
1820
1821        Returns:
1822            Alias: the subquery
1823        """
1824        instance = _maybe_copy(self, copy)
1825        return Subquery(
1826            this=instance,
1827            alias=TableAlias(this=to_identifier(alias)) if alias else None,
1828        )
1829
1830    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1831        raise NotImplementedError
1832
1833    @property
1834    def ctes(self):
1835        with_ = self.args.get("with")
1836        if not with_:
1837            return []
1838        return with_.expressions
1839
1840    @property
1841    def selects(self):
1842        raise NotImplementedError("Subqueryable objects must implement `selects`")
1843
1844    @property
1845    def named_selects(self):
1846        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1847
1848    def with_(
1849        self,
1850        alias,
1851        as_,
1852        recursive=None,
1853        append=True,
1854        dialect=None,
1855        copy=True,
1856        **opts,
1857    ):
1858        """
1859        Append to or set the common table expressions.
1860
1861        Example:
1862            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1863            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1864
1865        Args:
1866            alias (str | Expression): the SQL code string to parse as the table name.
1867                If an `Expression` instance is passed, this is used as-is.
1868            as_ (str | Expression): the SQL code string to parse as the table expression.
1869                If an `Expression` instance is passed, it will be used as-is.
1870            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1871            append (bool): if `True`, add to any existing expressions.
1872                Otherwise, this resets the expressions.
1873            dialect (str): the dialect used to parse the input expression.
1874            copy (bool): if `False`, modify this expression instance in-place.
1875            opts (kwargs): other options to use to parse the input expressions.
1876
1877        Returns:
1878            Select: the modified expression.
1879        """
1880        alias_expression = maybe_parse(
1881            alias,
1882            dialect=dialect,
1883            into=TableAlias,
1884            **opts,
1885        )
1886        as_expression = maybe_parse(
1887            as_,
1888            dialect=dialect,
1889            **opts,
1890        )
1891        cte = CTE(
1892            this=as_expression,
1893            alias=alias_expression,
1894        )
1895        return _apply_child_list_builder(
1896            cte,
1897            instance=self,
1898            arg="with",
1899            append=append,
1900            copy=copy,
1901            into=With,
1902            properties={"recursive": recursive or False},
1903        )
1904
1905
1906QUERY_MODIFIERS = {
1907    "match": False,
1908    "laterals": False,
1909    "joins": False,
1910    "pivots": False,
1911    "where": False,
1912    "group": False,
1913    "having": False,
1914    "qualify": False,
1915    "windows": False,
1916    "distribute": False,
1917    "sort": False,
1918    "cluster": False,
1919    "order": False,
1920    "limit": False,
1921    "offset": False,
1922    "lock": False,
1923    "sample": False,
1924}
1925
1926
1927class Table(Expression):
1928    arg_types = {
1929        "this": True,
1930        "alias": False,
1931        "db": False,
1932        "catalog": False,
1933        "laterals": False,
1934        "joins": False,
1935        "pivots": False,
1936        "hints": False,
1937        "system_time": False,
1938    }
1939
1940    @property
1941    def db(self) -> str:
1942        return self.text("db")
1943
1944    @property
1945    def catalog(self) -> str:
1946        return self.text("catalog")
1947
1948
1949# See the TSQL "Querying data in a system-versioned temporal table" page
1950class SystemTime(Expression):
1951    arg_types = {
1952        "this": False,
1953        "expression": False,
1954        "kind": True,
1955    }
1956
1957
1958class Union(Subqueryable):
1959    arg_types = {
1960        "with": False,
1961        "this": True,
1962        "expression": True,
1963        "distinct": False,
1964        **QUERY_MODIFIERS,
1965    }
1966
1967    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1968        """
1969        Set the LIMIT expression.
1970
1971        Example:
1972            >>> select("1").union(select("1")).limit(1).sql()
1973            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1974
1975        Args:
1976            expression (str | int | Expression): the SQL code string to parse.
1977                This can also be an integer.
1978                If a `Limit` instance is passed, this is used as-is.
1979                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1980            dialect (str): the dialect used to parse the input expression.
1981            copy (bool): if `False`, modify this expression instance in-place.
1982            opts (kwargs): other options to use to parse the input expressions.
1983
1984        Returns:
1985            Select: The limited subqueryable.
1986        """
1987        return (
1988            select("*")
1989            .from_(self.subquery(alias="_l_0", copy=copy))
1990            .limit(expression, dialect=dialect, copy=False, **opts)
1991        )
1992
1993    def select(
1994        self,
1995        *expressions: ExpOrStr,
1996        append: bool = True,
1997        dialect: DialectType = None,
1998        copy: bool = True,
1999        **opts,
2000    ) -> Union:
2001        """Append to or set the SELECT of the union recursively.
2002
2003        Example:
2004            >>> from sqlglot import parse_one
2005            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2006            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2007
2008        Args:
2009            *expressions: the SQL code strings to parse.
2010                If an `Expression` instance is passed, it will be used as-is.
2011            append: if `True`, add to any existing expressions.
2012                Otherwise, this resets the expressions.
2013            dialect: the dialect used to parse the input expressions.
2014            copy: if `False`, modify this expression instance in-place.
2015            opts: other options to use to parse the input expressions.
2016
2017        Returns:
2018            Union: the modified expression.
2019        """
2020        this = self.copy() if copy else self
2021        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2022        this.expression.unnest().select(
2023            *expressions, append=append, dialect=dialect, copy=False, **opts
2024        )
2025        return this
2026
2027    @property
2028    def named_selects(self):
2029        return self.this.unnest().named_selects
2030
2031    @property
2032    def is_star(self) -> bool:
2033        return self.this.is_star or self.expression.is_star
2034
2035    @property
2036    def selects(self):
2037        return self.this.unnest().selects
2038
2039    @property
2040    def left(self):
2041        return self.this
2042
2043    @property
2044    def right(self):
2045        return self.expression
2046
2047
2048class Except(Union):
2049    pass
2050
2051
2052class Intersect(Union):
2053    pass
2054
2055
2056class Unnest(UDTF):
2057    arg_types = {
2058        "expressions": True,
2059        "ordinality": False,
2060        "alias": False,
2061        "offset": False,
2062    }
2063
2064
2065class Update(Expression):
2066    arg_types = {
2067        "with": False,
2068        "this": False,
2069        "expressions": True,
2070        "from": False,
2071        "where": False,
2072        "returning": False,
2073    }
2074
2075
2076class Values(UDTF):
2077    arg_types = {
2078        "expressions": True,
2079        "ordinality": False,
2080        "alias": False,
2081    }
2082
2083
2084class Var(Expression):
2085    pass
2086
2087
2088class Schema(Expression):
2089    arg_types = {"this": False, "expressions": False}
2090
2091
2092# Used to represent the FOR UPDATE and FOR SHARE locking read types.
2093# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
2094class Lock(Expression):
2095    arg_types = {"update": True}
2096
2097
2098class Select(Subqueryable):
2099    arg_types = {
2100        "with": False,
2101        "kind": False,
2102        "expressions": False,
2103        "hint": False,
2104        "distinct": False,
2105        "into": False,
2106        "from": False,
2107        **QUERY_MODIFIERS,
2108    }
2109
2110    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2111        """
2112        Set the FROM expression.
2113
2114        Example:
2115            >>> Select().from_("tbl").select("x").sql()
2116            'SELECT x FROM tbl'
2117
2118        Args:
2119            *expressions (str | Expression): the SQL code strings to parse.
2120                If a `From` instance is passed, this is used as-is.
2121                If another `Expression` instance is passed, it will be wrapped in a `From`.
2122            append (bool): if `True`, add to any existing expressions.
2123                Otherwise, this flattens all the `From` expression into a single expression.
2124            dialect (str): the dialect used to parse the input expression.
2125            copy (bool): if `False`, modify this expression instance in-place.
2126            opts (kwargs): other options to use to parse the input expressions.
2127
2128        Returns:
2129            Select: the modified expression.
2130        """
2131        return _apply_child_list_builder(
2132            *expressions,
2133            instance=self,
2134            arg="from",
2135            append=append,
2136            copy=copy,
2137            prefix="FROM",
2138            into=From,
2139            dialect=dialect,
2140            **opts,
2141        )
2142
2143    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2144        """
2145        Set the GROUP BY expression.
2146
2147        Example:
2148            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2149            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2150
2151        Args:
2152            *expressions (str | Expression): the SQL code strings to parse.
2153                If a `Group` instance is passed, this is used as-is.
2154                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2155                If nothing is passed in then a group by is not applied to the expression
2156            append (bool): if `True`, add to any existing expressions.
2157                Otherwise, this flattens all the `Group` expression into a single expression.
2158            dialect (str): the dialect used to parse the input expression.
2159            copy (bool): if `False`, modify this expression instance in-place.
2160            opts (kwargs): other options to use to parse the input expressions.
2161
2162        Returns:
2163            Select: the modified expression.
2164        """
2165        if not expressions:
2166            return self if not copy else self.copy()
2167        return _apply_child_list_builder(
2168            *expressions,
2169            instance=self,
2170            arg="group",
2171            append=append,
2172            copy=copy,
2173            prefix="GROUP BY",
2174            into=Group,
2175            dialect=dialect,
2176            **opts,
2177        )
2178
2179    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2180        """
2181        Set the ORDER BY expression.
2182
2183        Example:
2184            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2185            'SELECT x FROM tbl ORDER BY x DESC'
2186
2187        Args:
2188            *expressions (str | Expression): the SQL code strings to parse.
2189                If a `Group` instance is passed, this is used as-is.
2190                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2191            append (bool): if `True`, add to any existing expressions.
2192                Otherwise, this flattens all the `Order` expression into a single expression.
2193            dialect (str): the dialect used to parse the input expression.
2194            copy (bool): if `False`, modify this expression instance in-place.
2195            opts (kwargs): other options to use to parse the input expressions.
2196
2197        Returns:
2198            Select: the modified expression.
2199        """
2200        return _apply_child_list_builder(
2201            *expressions,
2202            instance=self,
2203            arg="order",
2204            append=append,
2205            copy=copy,
2206            prefix="ORDER BY",
2207            into=Order,
2208            dialect=dialect,
2209            **opts,
2210        )
2211
2212    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2213        """
2214        Set the SORT BY expression.
2215
2216        Example:
2217            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2218            'SELECT x FROM tbl SORT BY x DESC'
2219
2220        Args:
2221            *expressions (str | Expression): the SQL code strings to parse.
2222                If a `Group` instance is passed, this is used as-is.
2223                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2224            append (bool): if `True`, add to any existing expressions.
2225                Otherwise, this flattens all the `Order` expression into a single expression.
2226            dialect (str): the dialect used to parse the input expression.
2227            copy (bool): if `False`, modify this expression instance in-place.
2228            opts (kwargs): other options to use to parse the input expressions.
2229
2230        Returns:
2231            Select: the modified expression.
2232        """
2233        return _apply_child_list_builder(
2234            *expressions,
2235            instance=self,
2236            arg="sort",
2237            append=append,
2238            copy=copy,
2239            prefix="SORT BY",
2240            into=Sort,
2241            dialect=dialect,
2242            **opts,
2243        )
2244
2245    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2246        """
2247        Set the CLUSTER BY expression.
2248
2249        Example:
2250            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2251            'SELECT x FROM tbl CLUSTER BY x DESC'
2252
2253        Args:
2254            *expressions (str | Expression): the SQL code strings to parse.
2255                If a `Group` instance is passed, this is used as-is.
2256                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2257            append (bool): if `True`, add to any existing expressions.
2258                Otherwise, this flattens all the `Order` expression into a single expression.
2259            dialect (str): the dialect used to parse the input expression.
2260            copy (bool): if `False`, modify this expression instance in-place.
2261            opts (kwargs): other options to use to parse the input expressions.
2262
2263        Returns:
2264            Select: the modified expression.
2265        """
2266        return _apply_child_list_builder(
2267            *expressions,
2268            instance=self,
2269            arg="cluster",
2270            append=append,
2271            copy=copy,
2272            prefix="CLUSTER BY",
2273            into=Cluster,
2274            dialect=dialect,
2275            **opts,
2276        )
2277
2278    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2279        """
2280        Set the LIMIT expression.
2281
2282        Example:
2283            >>> Select().from_("tbl").select("x").limit(10).sql()
2284            'SELECT x FROM tbl LIMIT 10'
2285
2286        Args:
2287            expression (str | int | Expression): the SQL code string to parse.
2288                This can also be an integer.
2289                If a `Limit` instance is passed, this is used as-is.
2290                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2291            dialect (str): the dialect used to parse the input expression.
2292            copy (bool): if `False`, modify this expression instance in-place.
2293            opts (kwargs): other options to use to parse the input expressions.
2294
2295        Returns:
2296            Select: the modified expression.
2297        """
2298        return _apply_builder(
2299            expression=expression,
2300            instance=self,
2301            arg="limit",
2302            into=Limit,
2303            prefix="LIMIT",
2304            dialect=dialect,
2305            copy=copy,
2306            **opts,
2307        )
2308
2309    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2310        """
2311        Set the OFFSET expression.
2312
2313        Example:
2314            >>> Select().from_("tbl").select("x").offset(10).sql()
2315            'SELECT x FROM tbl OFFSET 10'
2316
2317        Args:
2318            expression (str | int | Expression): the SQL code string to parse.
2319                This can also be an integer.
2320                If a `Offset` instance is passed, this is used as-is.
2321                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2322            dialect (str): the dialect used to parse the input expression.
2323            copy (bool): if `False`, modify this expression instance in-place.
2324            opts (kwargs): other options to use to parse the input expressions.
2325
2326        Returns:
2327            Select: the modified expression.
2328        """
2329        return _apply_builder(
2330            expression=expression,
2331            instance=self,
2332            arg="offset",
2333            into=Offset,
2334            prefix="OFFSET",
2335            dialect=dialect,
2336            copy=copy,
2337            **opts,
2338        )
2339
2340    def select(
2341        self,
2342        *expressions: ExpOrStr,
2343        append: bool = True,
2344        dialect: DialectType = None,
2345        copy: bool = True,
2346        **opts,
2347    ) -> Select:
2348        """
2349        Append to or set the SELECT expressions.
2350
2351        Example:
2352            >>> Select().select("x", "y").sql()
2353            'SELECT x, y'
2354
2355        Args:
2356            *expressions: the SQL code strings to parse.
2357                If an `Expression` instance is passed, it will be used as-is.
2358            append: if `True`, add to any existing expressions.
2359                Otherwise, this resets the expressions.
2360            dialect: the dialect used to parse the input expressions.
2361            copy: if `False`, modify this expression instance in-place.
2362            opts: other options to use to parse the input expressions.
2363
2364        Returns:
2365            Select: the modified expression.
2366        """
2367        return _apply_list_builder(
2368            *expressions,
2369            instance=self,
2370            arg="expressions",
2371            append=append,
2372            dialect=dialect,
2373            copy=copy,
2374            **opts,
2375        )
2376
2377    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2378        """
2379        Append to or set the LATERAL expressions.
2380
2381        Example:
2382            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2383            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2384
2385        Args:
2386            *expressions (str | Expression): the SQL code strings to parse.
2387                If an `Expression` instance is passed, it will be used as-is.
2388            append (bool): if `True`, add to any existing expressions.
2389                Otherwise, this resets the expressions.
2390            dialect (str): the dialect used to parse the input expressions.
2391            copy (bool): if `False`, modify this expression instance in-place.
2392            opts (kwargs): other options to use to parse the input expressions.
2393
2394        Returns:
2395            Select: the modified expression.
2396        """
2397        return _apply_list_builder(
2398            *expressions,
2399            instance=self,
2400            arg="laterals",
2401            append=append,
2402            into=Lateral,
2403            prefix="LATERAL VIEW",
2404            dialect=dialect,
2405            copy=copy,
2406            **opts,
2407        )
2408
2409    def join(
2410        self,
2411        expression,
2412        on=None,
2413        using=None,
2414        append=True,
2415        join_type=None,
2416        join_alias=None,
2417        dialect=None,
2418        copy=True,
2419        **opts,
2420    ) -> Select:
2421        """
2422        Append to or set the JOIN expressions.
2423
2424        Example:
2425            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2426            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2427
2428            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2429            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2430
2431            Use `join_type` to change the type of join:
2432
2433            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2434            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2435
2436        Args:
2437            expression (str | Expression): the SQL code string to parse.
2438                If an `Expression` instance is passed, it will be used as-is.
2439            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2440                If an `Expression` instance is passed, it will be used as-is.
2441            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2442                If an `Expression` instance is passed, it will be used as-is.
2443            append (bool): if `True`, add to any existing expressions.
2444                Otherwise, this resets the expressions.
2445            join_type (str): If set, alter the parsed join type
2446            dialect (str): the dialect used to parse the input expressions.
2447            copy (bool): if `False`, modify this expression instance in-place.
2448            opts (kwargs): other options to use to parse the input expressions.
2449
2450        Returns:
2451            Select: the modified expression.
2452        """
2453        parse_args = {"dialect": dialect, **opts}
2454
2455        try:
2456            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2457        except ParseError:
2458            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2459
2460        join = expression if isinstance(expression, Join) else Join(this=expression)
2461
2462        if isinstance(join.this, Select):
2463            join.this.replace(join.this.subquery())
2464
2465        if join_type:
2466            natural: t.Optional[Token]
2467            side: t.Optional[Token]
2468            kind: t.Optional[Token]
2469
2470            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2471
2472            if natural:
2473                join.set("natural", True)
2474            if side:
2475                join.set("side", side.text)
2476            if kind:
2477                join.set("kind", kind.text)
2478
2479        if on:
2480            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2481            join.set("on", on)
2482
2483        if using:
2484            join = _apply_list_builder(
2485                *ensure_collection(using),
2486                instance=join,
2487                arg="using",
2488                append=append,
2489                copy=copy,
2490                **opts,
2491            )
2492
2493        if join_alias:
2494            join.set("this", alias_(join.this, join_alias, table=True))
2495        return _apply_list_builder(
2496            join,
2497            instance=self,
2498            arg="joins",
2499            append=append,
2500            copy=copy,
2501            **opts,
2502        )
2503
2504    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2505        """
2506        Append to or set the WHERE expressions.
2507
2508        Example:
2509            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2510            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2511
2512        Args:
2513            *expressions (str | Expression): the SQL code strings to parse.
2514                If an `Expression` instance is passed, it will be used as-is.
2515                Multiple expressions are combined with an AND operator.
2516            append (bool): if `True`, AND the new expressions to any existing expression.
2517                Otherwise, this resets the expression.
2518            dialect (str): the dialect used to parse the input expressions.
2519            copy (bool): if `False`, modify this expression instance in-place.
2520            opts (kwargs): other options to use to parse the input expressions.
2521
2522        Returns:
2523            Select: the modified expression.
2524        """
2525        return _apply_conjunction_builder(
2526            *expressions,
2527            instance=self,
2528            arg="where",
2529            append=append,
2530            into=Where,
2531            dialect=dialect,
2532            copy=copy,
2533            **opts,
2534        )
2535
2536    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2537        """
2538        Append to or set the HAVING expressions.
2539
2540        Example:
2541            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2542            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2543
2544        Args:
2545            *expressions (str | Expression): the SQL code strings to parse.
2546                If an `Expression` instance is passed, it will be used as-is.
2547                Multiple expressions are combined with an AND operator.
2548            append (bool): if `True`, AND the new expressions to any existing expression.
2549                Otherwise, this resets the expression.
2550            dialect (str): the dialect used to parse the input expressions.
2551            copy (bool): if `False`, modify this expression instance in-place.
2552            opts (kwargs): other options to use to parse the input expressions.
2553
2554        Returns:
2555            Select: the modified expression.
2556        """
2557        return _apply_conjunction_builder(
2558            *expressions,
2559            instance=self,
2560            arg="having",
2561            append=append,
2562            into=Having,
2563            dialect=dialect,
2564            copy=copy,
2565            **opts,
2566        )
2567
2568    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2569        return _apply_list_builder(
2570            *expressions,
2571            instance=self,
2572            arg="windows",
2573            append=append,
2574            into=Window,
2575            dialect=dialect,
2576            copy=copy,
2577            **opts,
2578        )
2579
2580    def qualify(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2581        return _apply_conjunction_builder(
2582            *expressions,
2583            instance=self,
2584            arg="qualify",
2585            append=append,
2586            into=Qualify,
2587            dialect=dialect,
2588            copy=copy,
2589            **opts,
2590        )
2591
2592    def distinct(self, distinct=True, copy=True) -> Select:
2593        """
2594        Set the OFFSET expression.
2595
2596        Example:
2597            >>> Select().from_("tbl").select("x").distinct().sql()
2598            'SELECT DISTINCT x FROM tbl'
2599
2600        Args:
2601            distinct (bool): whether the Select should be distinct
2602            copy (bool): if `False`, modify this expression instance in-place.
2603
2604        Returns:
2605            Select: the modified expression.
2606        """
2607        instance = _maybe_copy(self, copy)
2608        instance.set("distinct", Distinct() if distinct else None)
2609        return instance
2610
2611    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2612        """
2613        Convert this expression to a CREATE TABLE AS statement.
2614
2615        Example:
2616            >>> Select().select("*").from_("tbl").ctas("x").sql()
2617            'CREATE TABLE x AS SELECT * FROM tbl'
2618
2619        Args:
2620            table (str | Expression): the SQL code string to parse as the table name.
2621                If another `Expression` instance is passed, it will be used as-is.
2622            properties (dict): an optional mapping of table properties
2623            dialect (str): the dialect used to parse the input table.
2624            copy (bool): if `False`, modify this expression instance in-place.
2625            opts (kwargs): other options to use to parse the input table.
2626
2627        Returns:
2628            Create: the CREATE TABLE AS expression
2629        """
2630        instance = _maybe_copy(self, copy)
2631        table_expression = maybe_parse(
2632            table,
2633            into=Table,
2634            dialect=dialect,
2635            **opts,
2636        )
2637        properties_expression = None
2638        if properties:
2639            properties_expression = Properties.from_dict(properties)
2640
2641        return Create(
2642            this=table_expression,
2643            kind="table",
2644            expression=instance,
2645            properties=properties_expression,
2646        )
2647
2648    def lock(self, update: bool = True, copy: bool = True) -> Select:
2649        """
2650        Set the locking read mode for this expression.
2651
2652        Examples:
2653            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2654            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2655
2656            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2657            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2658
2659        Args:
2660            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2661            copy: if `False`, modify this expression instance in-place.
2662
2663        Returns:
2664            The modified expression.
2665        """
2666
2667        inst = _maybe_copy(self, copy)
2668        inst.set("lock", Lock(update=update))
2669
2670        return inst
2671
2672    @property
2673    def named_selects(self) -> t.List[str]:
2674        return [e.output_name for e in self.expressions if e.alias_or_name]
2675
2676    @property
2677    def is_star(self) -> bool:
2678        return any(expression.is_star for expression in self.expressions)
2679
2680    @property
2681    def selects(self) -> t.List[Expression]:
2682        return self.expressions
2683
2684
2685class Subquery(DerivedTable, Unionable):
2686    arg_types = {
2687        "this": True,
2688        "alias": False,
2689        "with": False,
2690        **QUERY_MODIFIERS,
2691    }
2692
2693    def unnest(self):
2694        """
2695        Returns the first non subquery.
2696        """
2697        expression = self
2698        while isinstance(expression, Subquery):
2699            expression = expression.this
2700        return expression
2701
2702    @property
2703    def is_star(self) -> bool:
2704        return self.this.is_star
2705
2706    @property
2707    def output_name(self):
2708        return self.alias
2709
2710
2711class TableSample(Expression):
2712    arg_types = {
2713        "this": False,
2714        "method": False,
2715        "bucket_numerator": False,
2716        "bucket_denominator": False,
2717        "bucket_field": False,
2718        "percent": False,
2719        "rows": False,
2720        "size": False,
2721        "seed": False,
2722        "kind": False,
2723    }
2724
2725
2726class Tag(Expression):
2727    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2728
2729    arg_types = {
2730        "this": False,
2731        "prefix": False,
2732        "postfix": False,
2733    }
2734
2735
2736class Pivot(Expression):
2737    arg_types = {
2738        "this": False,
2739        "alias": False,
2740        "expressions": True,
2741        "field": True,
2742        "unpivot": True,
2743    }
2744
2745
2746class Window(Expression):
2747    arg_types = {
2748        "this": True,
2749        "partition_by": False,
2750        "order": False,
2751        "spec": False,
2752        "alias": False,
2753    }
2754
2755
2756class WindowSpec(Expression):
2757    arg_types = {
2758        "kind": False,
2759        "start": False,
2760        "start_side": False,
2761        "end": False,
2762        "end_side": False,
2763    }
2764
2765
2766class Where(Expression):
2767    pass
2768
2769
2770class Star(Expression):
2771    arg_types = {"except": False, "replace": False}
2772
2773    @property
2774    def name(self) -> str:
2775        return "*"
2776
2777    @property
2778    def output_name(self):
2779        return self.name
2780
2781
2782class Parameter(Expression):
2783    arg_types = {"this": True, "wrapped": False}
2784
2785
2786class SessionParameter(Expression):
2787    arg_types = {"this": True, "kind": False}
2788
2789
2790class Placeholder(Expression):
2791    arg_types = {"this": False}
2792
2793
2794class Null(Condition):
2795    arg_types: t.Dict[str, t.Any] = {}
2796
2797    @property
2798    def name(self) -> str:
2799        return "NULL"
2800
2801
2802class Boolean(Condition):
2803    pass
2804
2805
2806class DataType(Expression):
2807    arg_types = {
2808        "this": True,
2809        "expressions": False,
2810        "nested": False,
2811        "values": False,
2812        "prefix": False,
2813    }
2814
2815    class Type(AutoName):
2816        CHAR = auto()
2817        NCHAR = auto()
2818        VARCHAR = auto()
2819        NVARCHAR = auto()
2820        TEXT = auto()
2821        MEDIUMTEXT = auto()
2822        LONGTEXT = auto()
2823        MEDIUMBLOB = auto()
2824        LONGBLOB = auto()
2825        BINARY = auto()
2826        VARBINARY = auto()
2827        INT = auto()
2828        UINT = auto()
2829        TINYINT = auto()
2830        UTINYINT = auto()
2831        SMALLINT = auto()
2832        USMALLINT = auto()
2833        BIGINT = auto()
2834        UBIGINT = auto()
2835        FLOAT = auto()
2836        DOUBLE = auto()
2837        DECIMAL = auto()
2838        BIGDECIMAL = auto()
2839        BIT = auto()
2840        BOOLEAN = auto()
2841        JSON = auto()
2842        JSONB = auto()
2843        INTERVAL = auto()
2844        TIME = auto()
2845        TIMESTAMP = auto()
2846        TIMESTAMPTZ = auto()
2847        TIMESTAMPLTZ = auto()
2848        DATE = auto()
2849        DATETIME = auto()
2850        ARRAY = auto()
2851        MAP = auto()
2852        UUID = auto()
2853        GEOGRAPHY = auto()
2854        GEOMETRY = auto()
2855        STRUCT = auto()
2856        NULLABLE = auto()
2857        HLLSKETCH = auto()
2858        HSTORE = auto()
2859        SUPER = auto()
2860        SERIAL = auto()
2861        SMALLSERIAL = auto()
2862        BIGSERIAL = auto()
2863        XML = auto()
2864        UNIQUEIDENTIFIER = auto()
2865        MONEY = auto()
2866        SMALLMONEY = auto()
2867        ROWVERSION = auto()
2868        IMAGE = auto()
2869        VARIANT = auto()
2870        OBJECT = auto()
2871        INET = auto()
2872        NULL = auto()
2873        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2874
2875    TEXT_TYPES = {
2876        Type.CHAR,
2877        Type.NCHAR,
2878        Type.VARCHAR,
2879        Type.NVARCHAR,
2880        Type.TEXT,
2881    }
2882
2883    INTEGER_TYPES = {
2884        Type.INT,
2885        Type.TINYINT,
2886        Type.SMALLINT,
2887        Type.BIGINT,
2888    }
2889
2890    FLOAT_TYPES = {
2891        Type.FLOAT,
2892        Type.DOUBLE,
2893    }
2894
2895    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2896
2897    TEMPORAL_TYPES = {
2898        Type.TIMESTAMP,
2899        Type.TIMESTAMPTZ,
2900        Type.TIMESTAMPLTZ,
2901        Type.DATE,
2902        Type.DATETIME,
2903    }
2904
2905    @classmethod
2906    def build(
2907        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2908    ) -> DataType:
2909        from sqlglot import parse_one
2910
2911        if isinstance(dtype, str):
2912            if dtype.upper() in cls.Type.__members__:
2913                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2914            else:
2915                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2916            if data_type_exp is None:
2917                raise ValueError(f"Unparsable data type value: {dtype}")
2918        elif isinstance(dtype, DataType.Type):
2919            data_type_exp = DataType(this=dtype)
2920        elif isinstance(dtype, DataType):
2921            return dtype
2922        else:
2923            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2924        return DataType(**{**data_type_exp.args, **kwargs})
2925
2926    def is_type(self, dtype: DataType.Type) -> bool:
2927        return self.this == dtype
2928
2929
2930# https://www.postgresql.org/docs/15/datatype-pseudo.html
2931class PseudoType(Expression):
2932    pass
2933
2934
2935class StructKwarg(Expression):
2936    arg_types = {"this": True, "expression": True}
2937
2938
2939# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2940class SubqueryPredicate(Predicate):
2941    pass
2942
2943
2944class All(SubqueryPredicate):
2945    pass
2946
2947
2948class Any(SubqueryPredicate):
2949    pass
2950
2951
2952class Exists(SubqueryPredicate):
2953    pass
2954
2955
2956# Commands to interact with the databases or engines. For most of the command
2957# expressions we parse whatever comes after the command's name as a string.
2958class Command(Expression):
2959    arg_types = {"this": True, "expression": False}
2960
2961
2962class Transaction(Expression):
2963    arg_types = {"this": False, "modes": False}
2964
2965
2966class Commit(Expression):
2967    arg_types = {"chain": False}
2968
2969
2970class Rollback(Expression):
2971    arg_types = {"savepoint": False}
2972
2973
2974class AlterTable(Expression):
2975    arg_types = {"this": True, "actions": True, "exists": False}
2976
2977
2978class AddConstraint(Expression):
2979    arg_types = {"this": False, "expression": False, "enforced": False}
2980
2981
2982class DropPartition(Expression):
2983    arg_types = {"expressions": True, "exists": False}
2984
2985
2986# Binary expressions like (ADD a b)
2987class Binary(Expression):
2988    arg_types = {"this": True, "expression": True}
2989
2990    @property
2991    def left(self):
2992        return self.this
2993
2994    @property
2995    def right(self):
2996        return self.expression
2997
2998
2999class Add(Binary):
3000    pass
3001
3002
3003class Connector(Binary, Condition):
3004    pass
3005
3006
3007class And(Connector):
3008    pass
3009
3010
3011class Or(Connector):
3012    pass
3013
3014
3015class BitwiseAnd(Binary):
3016    pass
3017
3018
3019class BitwiseLeftShift(Binary):
3020    pass
3021
3022
3023class BitwiseOr(Binary):
3024    pass
3025
3026
3027class BitwiseRightShift(Binary):
3028    pass
3029
3030
3031class BitwiseXor(Binary):
3032    pass
3033
3034
3035class Div(Binary):
3036    pass
3037
3038
3039class Overlaps(Binary):
3040    pass
3041
3042
3043class Dot(Binary):
3044    @property
3045    def name(self) -> str:
3046        return self.expression.name
3047
3048    @classmethod
3049    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3050        """Build a Dot object with a sequence of expressions."""
3051        if len(expressions) < 2:
3052            raise ValueError(f"Dot requires >= 2 expressions.")
3053
3054        a, b, *expressions = expressions
3055        dot = Dot(this=a, expression=b)
3056
3057        for expression in expressions:
3058            dot = Dot(this=dot, expression=expression)
3059
3060        return dot
3061
3062
3063class DPipe(Binary):
3064    pass
3065
3066
3067class EQ(Binary, Predicate):
3068    pass
3069
3070
3071class NullSafeEQ(Binary, Predicate):
3072    pass
3073
3074
3075class NullSafeNEQ(Binary, Predicate):
3076    pass
3077
3078
3079class Distance(Binary):
3080    pass
3081
3082
3083class Escape(Binary):
3084    pass
3085
3086
3087class Glob(Binary, Predicate):
3088    pass
3089
3090
3091class GT(Binary, Predicate):
3092    pass
3093
3094
3095class GTE(Binary, Predicate):
3096    pass
3097
3098
3099class ILike(Binary, Predicate):
3100    pass
3101
3102
3103class ILikeAny(Binary, Predicate):
3104    pass
3105
3106
3107class IntDiv(Binary):
3108    pass
3109
3110
3111class Is(Binary, Predicate):
3112    pass
3113
3114
3115class Kwarg(Binary):
3116    """Kwarg in special functions like func(kwarg => y)."""
3117
3118
3119class Like(Binary, Predicate):
3120    pass
3121
3122
3123class LikeAny(Binary, Predicate):
3124    pass
3125
3126
3127class LT(Binary, Predicate):
3128    pass
3129
3130
3131class LTE(Binary, Predicate):
3132    pass
3133
3134
3135class Mod(Binary):
3136    pass
3137
3138
3139class Mul(Binary):
3140    pass
3141
3142
3143class NEQ(Binary, Predicate):
3144    pass
3145
3146
3147class SimilarTo(Binary, Predicate):
3148    pass
3149
3150
3151class Slice(Binary):
3152    arg_types = {"this": False, "expression": False}
3153
3154
3155class Sub(Binary):
3156    pass
3157
3158
3159class ArrayOverlaps(Binary):
3160    pass
3161
3162
3163# Unary Expressions
3164# (NOT a)
3165class Unary(Expression):
3166    pass
3167
3168
3169class BitwiseNot(Unary):
3170    pass
3171
3172
3173class Not(Unary, Condition):
3174    pass
3175
3176
3177class Paren(Unary, Condition):
3178    arg_types = {"this": True, "with": False}
3179
3180
3181class Neg(Unary):
3182    pass
3183
3184
3185class Alias(Expression):
3186    arg_types = {"this": True, "alias": False}
3187
3188    @property
3189    def output_name(self):
3190        return self.alias
3191
3192
3193class Aliases(Expression):
3194    arg_types = {"this": True, "expressions": True}
3195
3196    @property
3197    def aliases(self):
3198        return self.expressions
3199
3200
3201class AtTimeZone(Expression):
3202    arg_types = {"this": True, "zone": True}
3203
3204
3205class Between(Predicate):
3206    arg_types = {"this": True, "low": True, "high": True}
3207
3208
3209class Bracket(Condition):
3210    arg_types = {"this": True, "expressions": True}
3211
3212
3213class Distinct(Expression):
3214    arg_types = {"expressions": False, "on": False}
3215
3216
3217class In(Predicate):
3218    arg_types = {
3219        "this": True,
3220        "expressions": False,
3221        "query": False,
3222        "unnest": False,
3223        "field": False,
3224        "is_global": False,
3225    }
3226
3227
3228class TimeUnit(Expression):
3229    """Automatically converts unit arg into a var."""
3230
3231    arg_types = {"unit": False}
3232
3233    def __init__(self, **args):
3234        unit = args.get("unit")
3235        if isinstance(unit, (Column, Literal)):
3236            args["unit"] = Var(this=unit.name)
3237        elif isinstance(unit, Week):
3238            unit.set("this", Var(this=unit.this.name))
3239        super().__init__(**args)
3240
3241
3242class Interval(TimeUnit):
3243    arg_types = {"this": False, "unit": False}
3244
3245
3246class IgnoreNulls(Expression):
3247    pass
3248
3249
3250class RespectNulls(Expression):
3251    pass
3252
3253
3254# Functions
3255class Func(Condition):
3256    """
3257    The base class for all function expressions.
3258
3259    Attributes:
3260        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3261            treated as a variable length argument and the argument's value will be stored as a list.
3262        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3263            for this function expression. These values are used to map this node to a name during parsing
3264            as well as to provide the function's name during SQL string generation. By default the SQL
3265            name is set to the expression's class name transformed to snake case.
3266    """
3267
3268    is_var_len_args = False
3269
3270    @classmethod
3271    def from_arg_list(cls, args):
3272        if cls.is_var_len_args:
3273            all_arg_keys = list(cls.arg_types)
3274            # If this function supports variable length argument treat the last argument as such.
3275            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3276            num_non_var = len(non_var_len_arg_keys)
3277
3278            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3279            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3280        else:
3281            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3282
3283        return cls(**args_dict)
3284
3285    @classmethod
3286    def sql_names(cls):
3287        if cls is Func:
3288            raise NotImplementedError(
3289                "SQL name is only supported by concrete function implementations"
3290            )
3291        if "_sql_names" not in cls.__dict__:
3292            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3293        return cls._sql_names
3294
3295    @classmethod
3296    def sql_name(cls):
3297        return cls.sql_names()[0]
3298
3299    @classmethod
3300    def default_parser_mappings(cls):
3301        return {name: cls.from_arg_list for name in cls.sql_names()}
3302
3303
3304class AggFunc(Func):
3305    pass
3306
3307
3308class Abs(Func):
3309    pass
3310
3311
3312class Anonymous(Func):
3313    arg_types = {"this": True, "expressions": False}
3314    is_var_len_args = True
3315
3316
3317# https://docs.snowflake.com/en/sql-reference/functions/hll
3318# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
3319class Hll(AggFunc):
3320    arg_types = {"this": True, "expressions": False}
3321    is_var_len_args = True
3322
3323
3324class ApproxDistinct(AggFunc):
3325    arg_types = {"this": True, "accuracy": False}
3326
3327
3328class Array(Func):
3329    arg_types = {"expressions": False}
3330    is_var_len_args = True
3331
3332
3333# https://docs.snowflake.com/en/sql-reference/functions/to_char
3334class ToChar(Func):
3335    arg_types = {"this": True, "format": False}
3336
3337
3338class GenerateSeries(Func):
3339    arg_types = {"start": True, "end": True, "step": False}
3340
3341
3342class ArrayAgg(AggFunc):
3343    pass
3344
3345
3346class ArrayAll(Func):
3347    arg_types = {"this": True, "expression": True}
3348
3349
3350class ArrayAny(Func):
3351    arg_types = {"this": True, "expression": True}
3352
3353
3354class ArrayConcat(Func):
3355    arg_types = {"this": True, "expressions": False}
3356    is_var_len_args = True
3357
3358
3359class ArrayContains(Binary, Func):
3360    pass
3361
3362
3363class ArrayContained(Binary):
3364    pass
3365
3366
3367class ArrayFilter(Func):
3368    arg_types = {"this": True, "expression": True}
3369    _sql_names = ["FILTER", "ARRAY_FILTER"]
3370
3371
3372class ArrayJoin(Func):
3373    arg_types = {"this": True, "expression": True, "null": False}
3374
3375
3376class ArraySize(Func):
3377    arg_types = {"this": True, "expression": False}
3378
3379
3380class ArraySort(Func):
3381    arg_types = {"this": True, "expression": False}
3382
3383
3384class ArraySum(Func):
3385    pass
3386
3387
3388class ArrayUnionAgg(AggFunc):
3389    pass
3390
3391
3392class Avg(AggFunc):
3393    pass
3394
3395
3396class AnyValue(AggFunc):
3397    pass
3398
3399
3400class Case(Func):
3401    arg_types = {"this": False, "ifs": True, "default": False}
3402
3403
3404class Cast(Func):
3405    arg_types = {"this": True, "to": True}
3406
3407    @property
3408    def name(self) -> str:
3409        return self.this.name
3410
3411    @property
3412    def to(self):
3413        return self.args["to"]
3414
3415    @property
3416    def output_name(self):
3417        return self.name
3418
3419    def is_type(self, dtype: DataType.Type) -> bool:
3420        return self.to.is_type(dtype)
3421
3422
3423class Collate(Binary):
3424    pass
3425
3426
3427class TryCast(Cast):
3428    pass
3429
3430
3431class Ceil(Func):
3432    arg_types = {"this": True, "decimals": False}
3433    _sql_names = ["CEIL", "CEILING"]
3434
3435
3436class Coalesce(Func):
3437    arg_types = {"this": True, "expressions": False}
3438    is_var_len_args = True
3439
3440
3441class Concat(Func):
3442    arg_types = {"expressions": True}
3443    is_var_len_args = True
3444
3445
3446class ConcatWs(Concat):
3447    _sql_names = ["CONCAT_WS"]
3448
3449
3450class Count(AggFunc):
3451    arg_types = {"this": False}
3452
3453
3454class CountIf(AggFunc):
3455    pass
3456
3457
3458class CurrentDate(Func):
3459    arg_types = {"this": False}
3460
3461
3462class CurrentDatetime(Func):
3463    arg_types = {"this": False}
3464
3465
3466class CurrentTime(Func):
3467    arg_types = {"this": False}
3468
3469
3470class CurrentTimestamp(Func):
3471    arg_types = {"this": False}
3472
3473
3474class CurrentUser(Func):
3475    arg_types = {"this": False}
3476
3477
3478class DateAdd(Func, TimeUnit):
3479    arg_types = {"this": True, "expression": True, "unit": False}
3480
3481
3482class DateSub(Func, TimeUnit):
3483    arg_types = {"this": True, "expression": True, "unit": False}
3484
3485
3486class DateDiff(Func, TimeUnit):
3487    _sql_names = ["DATEDIFF", "DATE_DIFF"]
3488    arg_types = {"this": True, "expression": True, "unit": False}
3489
3490
3491class DateTrunc(Func):
3492    arg_types = {"unit": True, "this": True, "zone": False}
3493
3494
3495class DatetimeAdd(Func, TimeUnit):
3496    arg_types = {"this": True, "expression": True, "unit": False}
3497
3498
3499class DatetimeSub(Func, TimeUnit):
3500    arg_types = {"this": True, "expression": True, "unit": False}
3501
3502
3503class DatetimeDiff(Func, TimeUnit):
3504    arg_types = {"this": True, "expression": True, "unit": False}
3505
3506
3507class DatetimeTrunc(Func, TimeUnit):
3508    arg_types = {"this": True, "unit": True, "zone": False}
3509
3510
3511class DayOfWeek(Func):
3512    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3513
3514
3515class DayOfMonth(Func):
3516    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3517
3518
3519class DayOfYear(Func):
3520    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3521
3522
3523class WeekOfYear(Func):
3524    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3525
3526
3527class LastDateOfMonth(Func):
3528    pass
3529
3530
3531class Extract(Func):
3532    arg_types = {"this": True, "expression": True}
3533
3534
3535class TimestampAdd(Func, TimeUnit):
3536    arg_types = {"this": True, "expression": True, "unit": False}
3537
3538
3539class TimestampSub(Func, TimeUnit):
3540    arg_types = {"this": True, "expression": True, "unit": False}
3541
3542
3543class TimestampDiff(Func, TimeUnit):
3544    arg_types = {"this": True, "expression": True, "unit": False}
3545
3546
3547class TimestampTrunc(Func, TimeUnit):
3548    arg_types = {"this": True, "unit": True, "zone": False}
3549
3550
3551class TimeAdd(Func, TimeUnit):
3552    arg_types = {"this": True, "expression": True, "unit": False}
3553
3554
3555class TimeSub(Func, TimeUnit):
3556    arg_types = {"this": True, "expression": True, "unit": False}
3557
3558
3559class TimeDiff(Func, TimeUnit):
3560    arg_types = {"this": True, "expression": True, "unit": False}
3561
3562
3563class TimeTrunc(Func, TimeUnit):
3564    arg_types = {"this": True, "unit": True, "zone": False}
3565
3566
3567class DateFromParts(Func):
3568    _sql_names = ["DATEFROMPARTS"]
3569    arg_types = {"year": True, "month": True, "day": True}
3570
3571
3572class DateStrToDate(Func):
3573    pass
3574
3575
3576class DateToDateStr(Func):
3577    pass
3578
3579
3580class DateToDi(Func):
3581    pass
3582
3583
3584class Day(Func):
3585    pass
3586
3587
3588class Decode(Func):
3589    arg_types = {"this": True, "charset": True, "replace": False}
3590
3591
3592class DiToDate(Func):
3593    pass
3594
3595
3596class Encode(Func):
3597    arg_types = {"this": True, "charset": True}
3598
3599
3600class Exp(Func):
3601    pass
3602
3603
3604class Explode(Func):
3605    pass
3606
3607
3608class ExponentialTimeDecayedAvg(AggFunc):
3609    arg_types = {"this": True, "time": False, "decay": False}
3610
3611
3612class Floor(Func):
3613    arg_types = {"this": True, "decimals": False}
3614
3615
3616class Greatest(Func):
3617    arg_types = {"this": True, "expressions": False}
3618    is_var_len_args = True
3619
3620
3621class GroupConcat(Func):
3622    arg_types = {"this": True, "separator": False}
3623
3624
3625class GroupUniqArray(AggFunc):
3626    arg_types = {"this": True, "size": False}
3627
3628
3629class Hex(Func):
3630    pass
3631
3632
3633class Histogram(AggFunc):
3634    arg_types = {"this": True, "bins": False}
3635
3636
3637class If(Func):
3638    arg_types = {"this": True, "true": True, "false": False}
3639
3640
3641class IfNull(Func):
3642    arg_types = {"this": True, "expression": False}
3643    _sql_names = ["IFNULL", "NVL"]
3644
3645
3646class Initcap(Func):
3647    pass
3648
3649
3650class JSONKeyValue(Expression):
3651    arg_types = {"this": True, "expression": True}
3652
3653
3654class JSONObject(Func):
3655    arg_types = {
3656        "expressions": False,
3657        "null_handling": False,
3658        "unique_keys": False,
3659        "return_type": False,
3660        "format_json": False,
3661        "encoding": False,
3662    }
3663
3664
3665class JSONBContains(Binary):
3666    _sql_names = ["JSONB_CONTAINS"]
3667
3668
3669class JSONExtract(Binary, Func):
3670    _sql_names = ["JSON_EXTRACT"]
3671
3672
3673class JSONExtractScalar(JSONExtract):
3674    _sql_names = ["JSON_EXTRACT_SCALAR"]
3675
3676
3677class JSONBExtract(JSONExtract):
3678    _sql_names = ["JSONB_EXTRACT"]
3679
3680
3681class JSONBExtractScalar(JSONExtract):
3682    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3683
3684
3685class JSONFormat(Func):
3686    arg_types = {"this": False, "options": False}
3687    _sql_names = ["JSON_FORMAT"]
3688
3689
3690class Least(Func):
3691    arg_types = {"expressions": False}
3692    is_var_len_args = True
3693
3694
3695class Length(Func):
3696    pass
3697
3698
3699class Levenshtein(Func):
3700    arg_types = {
3701        "this": True,
3702        "expression": False,
3703        "ins_cost": False,
3704        "del_cost": False,
3705        "sub_cost": False,
3706    }
3707
3708
3709class Ln(Func):
3710    pass
3711
3712
3713class Log(Func):
3714    arg_types = {"this": True, "expression": False}
3715
3716
3717class Log2(Func):
3718    pass
3719
3720
3721class Log10(Func):
3722    pass
3723
3724
3725class LogicalOr(AggFunc):
3726    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
3727
3728
3729class LogicalAnd(AggFunc):
3730    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
3731
3732
3733class Lower(Func):
3734    _sql_names = ["LOWER", "LCASE"]
3735
3736
3737class Map(Func):
3738    arg_types = {"keys": False, "values": False}
3739
3740
3741class VarMap(Func):
3742    arg_types = {"keys": True, "values": True}
3743    is_var_len_args = True
3744
3745
3746# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
3747class MatchAgainst(Func):
3748    arg_types = {"this": True, "expressions": True, "modifier": False}
3749
3750
3751class Max(AggFunc):
3752    arg_types = {"this": True, "expressions": False}
3753    is_var_len_args = True
3754
3755
3756class Min(AggFunc):
3757    arg_types = {"this": True, "expressions": False}
3758    is_var_len_args = True
3759
3760
3761class Month(Func):
3762    pass
3763
3764
3765class Nvl2(Func):
3766    arg_types = {"this": True, "true": True, "false": False}
3767
3768
3769class Posexplode(Func):
3770    pass
3771
3772
3773class Pow(Binary, Func):
3774    _sql_names = ["POWER", "POW"]
3775
3776
3777class PercentileCont(AggFunc):
3778    pass
3779
3780
3781class PercentileDisc(AggFunc):
3782    pass
3783
3784
3785class Quantile(AggFunc):
3786    arg_types = {"this": True, "quantile": True}
3787
3788
3789# Clickhouse-specific:
3790# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3791class Quantiles(AggFunc):
3792    arg_types = {"parameters": True, "expressions": True}
3793    is_var_len_args = True
3794
3795
3796class QuantileIf(AggFunc):
3797    arg_types = {"parameters": True, "expressions": True}
3798
3799
3800class ApproxQuantile(Quantile):
3801    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3802
3803
3804class RangeN(Func):
3805    arg_types = {"this": True, "expressions": True, "each": False}
3806
3807
3808class ReadCSV(Func):
3809    _sql_names = ["READ_CSV"]
3810    is_var_len_args = True
3811    arg_types = {"this": True, "expressions": False}
3812
3813
3814class Reduce(Func):
3815    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3816
3817
3818class RegexpExtract(Func):
3819    arg_types = {
3820        "this": True,
3821        "expression": True,
3822        "position": False,
3823        "occurrence": False,
3824        "group": False,
3825    }
3826
3827
3828class RegexpLike(Func):
3829    arg_types = {"this": True, "expression": True, "flag": False}
3830
3831
3832class RegexpILike(Func):
3833    arg_types = {"this": True, "expression": True, "flag": False}
3834
3835
3836# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
3837# limit is the number of times a pattern is applied
3838class RegexpSplit(Func):
3839    arg_types = {"this": True, "expression": True, "limit": False}
3840
3841
3842class Repeat(Func):
3843    arg_types = {"this": True, "times": True}
3844
3845
3846class Round(Func):
3847    arg_types = {"this": True, "decimals": False}
3848
3849
3850class RowNumber(Func):
3851    arg_types: t.Dict[str, t.Any] = {}
3852
3853
3854class SafeDivide(Func):
3855    arg_types = {"this": True, "expression": True}
3856
3857
3858class SetAgg(AggFunc):
3859    pass
3860
3861
3862class SortArray(Func):
3863    arg_types = {"this": True, "asc": False}
3864
3865
3866class Split(Func):
3867    arg_types = {"this": True, "expression": True, "limit": False}
3868
3869
3870# Start may be omitted in the case of postgres
3871# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3872class Substring(Func):
3873    arg_types = {"this": True, "start": False, "length": False}
3874
3875
3876class StrPosition(Func):
3877    arg_types = {
3878        "this": True,
3879        "substr": True,
3880        "position": False,
3881        "instance": False,
3882    }
3883
3884
3885class StrToDate(Func):
3886    arg_types = {"this": True, "format": True}
3887
3888
3889class StrToTime(Func):
3890    arg_types = {"this": True, "format": True}
3891
3892
3893# Spark allows unix_timestamp()
3894# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3895class StrToUnix(Func):
3896    arg_types = {"this": False, "format": False}
3897
3898
3899class NumberToStr(Func):
3900    arg_types = {"this": True, "format": True}
3901
3902
3903class Struct(Func):
3904    arg_types = {"expressions": True}
3905    is_var_len_args = True
3906
3907
3908class StructExtract(Func):
3909    arg_types = {"this": True, "expression": True}
3910
3911
3912class Sum(AggFunc):
3913    pass
3914
3915
3916class Sqrt(Func):
3917    pass
3918
3919
3920class Stddev(AggFunc):
3921    pass
3922
3923
3924class StddevPop(AggFunc):
3925    pass
3926
3927
3928class StddevSamp(AggFunc):
3929    pass
3930
3931
3932class TimeToStr(Func):
3933    arg_types = {"this": True, "format": True}
3934
3935
3936class TimeToTimeStr(Func):
3937    pass
3938
3939
3940class TimeToUnix(Func):
3941    pass
3942
3943
3944class TimeStrToDate(Func):
3945    pass
3946
3947
3948class TimeStrToTime(Func):
3949    pass
3950
3951
3952class TimeStrToUnix(Func):
3953    pass
3954
3955
3956class Trim(Func):
3957    arg_types = {
3958        "this": True,
3959        "expression": False,
3960        "position": False,
3961        "collation": False,
3962    }
3963
3964
3965class TsOrDsAdd(Func, TimeUnit):
3966    arg_types = {"this": True, "expression": True, "unit": False}
3967
3968
3969class TsOrDsToDateStr(Func):
3970    pass
3971
3972
3973class TsOrDsToDate(Func):
3974    arg_types = {"this": True, "format": False}
3975
3976
3977class TsOrDiToDi(Func):
3978    pass
3979
3980
3981class Unhex(Func):
3982    pass
3983
3984
3985class UnixToStr(Func):
3986    arg_types = {"this": True, "format": False}
3987
3988
3989# https://prestodb.io/docs/current/functions/datetime.html
3990# presto has weird zone/hours/minutes
3991class UnixToTime(Func):
3992    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3993
3994    SECONDS = Literal.string("seconds")
3995    MILLIS = Literal.string("millis")
3996    MICROS = Literal.string("micros")
3997
3998
3999class UnixToTimeStr(Func):
4000    pass
4001
4002
4003class Upper(Func):
4004    _sql_names = ["UPPER", "UCASE"]
4005
4006
4007class Variance(AggFunc):
4008    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4009
4010
4011class VariancePop(AggFunc):
4012    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4013
4014
4015class Week(Func):
4016    arg_types = {"this": True, "mode": False}
4017
4018
4019class XMLTable(Func):
4020    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
4021
4022
4023class Year(Func):
4024    pass
4025
4026
4027class Use(Expression):
4028    arg_types = {"this": True, "kind": False}
4029
4030
4031class Merge(Expression):
4032    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
4033
4034
4035class When(Func):
4036    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
4037
4038
4039def _norm_arg(arg):
4040    return arg.lower() if type(arg) is str else arg
4041
4042
4043ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
4044
4045
4046# Helpers
4047def maybe_parse(
4048    sql_or_expression: ExpOrStr,
4049    *,
4050    into: t.Optional[IntoType] = None,
4051    dialect: DialectType = None,
4052    prefix: t.Optional[str] = None,
4053    copy: bool = False,
4054    **opts,
4055) -> Expression:
4056    """Gracefully handle a possible string or expression.
4057
4058    Example:
4059        >>> maybe_parse("1")
4060        (LITERAL this: 1, is_string: False)
4061        >>> maybe_parse(to_identifier("x"))
4062        (IDENTIFIER this: x, quoted: False)
4063
4064    Args:
4065        sql_or_expression: the SQL code string or an expression
4066        into: the SQLGlot Expression to parse into
4067        dialect: the dialect used to parse the input expressions (in the case that an
4068            input expression is a SQL string).
4069        prefix: a string to prefix the sql with before it gets parsed
4070            (automatically includes a space)
4071        copy: whether or not to copy the expression.
4072        **opts: other options to use to parse the input expressions (again, in the case
4073            that an input expression is a SQL string).
4074
4075    Returns:
4076        Expression: the parsed or given expression.
4077    """
4078    if isinstance(sql_or_expression, Expression):
4079        if copy:
4080            return sql_or_expression.copy()
4081        return sql_or_expression
4082
4083    import sqlglot
4084
4085    sql = str(sql_or_expression)
4086    if prefix:
4087        sql = f"{prefix} {sql}"
4088    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
4089
4090
4091def _maybe_copy(instance, copy=True):
4092    return instance.copy() if copy else instance
4093
4094
4095def _is_wrong_expression(expression, into):
4096    return isinstance(expression, Expression) and not isinstance(expression, into)
4097
4098
4099def _apply_builder(
4100    expression,
4101    instance,
4102    arg,
4103    copy=True,
4104    prefix=None,
4105    into=None,
4106    dialect=None,
4107    **opts,
4108):
4109    if _is_wrong_expression(expression, into):
4110        expression = into(this=expression)
4111    instance = _maybe_copy(instance, copy)
4112    expression = maybe_parse(
4113        sql_or_expression=expression,
4114        prefix=prefix,
4115        into=into,
4116        dialect=dialect,
4117        **opts,
4118    )
4119    instance.set(arg, expression)
4120    return instance
4121
4122
4123def _apply_child_list_builder(
4124    *expressions,
4125    instance,
4126    arg,
4127    append=True,
4128    copy=True,
4129    prefix=None,
4130    into=None,
4131    dialect=None,
4132    properties=None,
4133    **opts,
4134):
4135    instance = _maybe_copy(instance, copy)
4136    parsed = []
4137    for expression in expressions:
4138        if _is_wrong_expression(expression, into):
4139            expression = into(expressions=[expression])
4140        expression = maybe_parse(
4141            expression,
4142            into=into,
4143            dialect=dialect,
4144            prefix=prefix,
4145            **opts,
4146        )
4147        parsed.extend(expression.expressions)
4148
4149    existing = instance.args.get(arg)
4150    if append and existing:
4151        parsed = existing.expressions + parsed
4152
4153    child = into(expressions=parsed)
4154    for k, v in (properties or {}).items():
4155        child.set(k, v)
4156    instance.set(arg, child)
4157    return instance
4158
4159
4160def _apply_list_builder(
4161    *expressions,
4162    instance,
4163    arg,
4164    append=True,
4165    copy=True,
4166    prefix=None,
4167    into=None,
4168    dialect=None,
4169    **opts,
4170):
4171    inst = _maybe_copy(instance, copy)
4172
4173    expressions = [
4174        maybe_parse(
4175            sql_or_expression=expression,
4176            into=into,
4177            prefix=prefix,
4178            dialect=dialect,
4179            **opts,
4180        )
4181        for expression in expressions
4182    ]
4183
4184    existing_expressions = inst.args.get(arg)
4185    if append and existing_expressions:
4186        expressions = existing_expressions + expressions
4187
4188    inst.set(arg, expressions)
4189    return inst
4190
4191
4192def _apply_conjunction_builder(
4193    *expressions,
4194    instance,
4195    arg,
4196    into=None,
4197    append=True,
4198    copy=True,
4199    dialect=None,
4200    **opts,
4201):
4202    expressions = [exp for exp in expressions if exp is not None and exp != ""]
4203    if not expressions:
4204        return instance
4205
4206    inst = _maybe_copy(instance, copy)
4207
4208    existing = inst.args.get(arg)
4209    if append and existing is not None:
4210        expressions = [existing.this if into else existing] + list(expressions)
4211
4212    node = and_(*expressions, dialect=dialect, **opts)
4213
4214    inst.set(arg, into(this=node) if into else node)
4215    return inst
4216
4217
4218def _combine(expressions, operator, dialect=None, **opts):
4219    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
4220    this = expressions[0]
4221    if expressions[1:]:
4222        this = _wrap_operator(this)
4223    for expression in expressions[1:]:
4224        this = operator(this=this, expression=_wrap_operator(expression))
4225    return this
4226
4227
4228def _wrap_operator(expression):
4229    if isinstance(expression, (And, Or, Not)):
4230        expression = Paren(this=expression)
4231    return expression
4232
4233
4234def union(left, right, distinct=True, dialect=None, **opts):
4235    """
4236    Initializes a syntax tree from one UNION expression.
4237
4238    Example:
4239        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
4240        'SELECT * FROM foo UNION SELECT * FROM bla'
4241
4242    Args:
4243        left (str | Expression): the SQL code string corresponding to the left-hand side.
4244            If an `Expression` instance is passed, it will be used as-is.
4245        right (str | Expression): the SQL code string corresponding to the right-hand side.
4246            If an `Expression` instance is passed, it will be used as-is.
4247        distinct (bool): set the DISTINCT flag if and only if this is true.
4248        dialect (str): the dialect used to parse the input expression.
4249        opts (kwargs): other options to use to parse the input expressions.
4250    Returns:
4251        Union: the syntax tree for the UNION expression.
4252    """
4253    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4254    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4255
4256    return Union(this=left, expression=right, distinct=distinct)
4257
4258
4259def intersect(left, right, distinct=True, dialect=None, **opts):
4260    """
4261    Initializes a syntax tree from one INTERSECT expression.
4262
4263    Example:
4264        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4265        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4266
4267    Args:
4268        left (str | Expression): the SQL code string corresponding to the left-hand side.
4269            If an `Expression` instance is passed, it will be used as-is.
4270        right (str | Expression): the SQL code string corresponding to the right-hand side.
4271            If an `Expression` instance is passed, it will be used as-is.
4272        distinct (bool): set the DISTINCT flag if and only if this is true.
4273        dialect (str): the dialect used to parse the input expression.
4274        opts (kwargs): other options to use to parse the input expressions.
4275    Returns:
4276        Intersect: the syntax tree for the INTERSECT expression.
4277    """
4278    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4279    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4280
4281    return Intersect(this=left, expression=right, distinct=distinct)
4282
4283
4284def except_(left, right, distinct=True, dialect=None, **opts):
4285    """
4286    Initializes a syntax tree from one EXCEPT expression.
4287
4288    Example:
4289        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4290        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4291
4292    Args:
4293        left (str | Expression): the SQL code string corresponding to the left-hand side.
4294            If an `Expression` instance is passed, it will be used as-is.
4295        right (str | Expression): the SQL code string corresponding to the right-hand side.
4296            If an `Expression` instance is passed, it will be used as-is.
4297        distinct (bool): set the DISTINCT flag if and only if this is true.
4298        dialect (str): the dialect used to parse the input expression.
4299        opts (kwargs): other options to use to parse the input expressions.
4300    Returns:
4301        Except: the syntax tree for the EXCEPT statement.
4302    """
4303    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4304    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4305
4306    return Except(this=left, expression=right, distinct=distinct)
4307
4308
4309def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
4310    """
4311    Initializes a syntax tree from one or multiple SELECT expressions.
4312
4313    Example:
4314        >>> select("col1", "col2").from_("tbl").sql()
4315        'SELECT col1, col2 FROM tbl'
4316
4317    Args:
4318        *expressions: the SQL code string to parse as the expressions of a
4319            SELECT statement. If an Expression instance is passed, this is used as-is.
4320        dialect: the dialect used to parse the input expressions (in the case that an
4321            input expression is a SQL string).
4322        **opts: other options to use to parse the input expressions (again, in the case
4323            that an input expression is a SQL string).
4324
4325    Returns:
4326        Select: the syntax tree for the SELECT statement.
4327    """
4328    return Select().select(*expressions, dialect=dialect, **opts)
4329
4330
4331def from_(*expressions, dialect=None, **opts) -> Select:
4332    """
4333    Initializes a syntax tree from a FROM expression.
4334
4335    Example:
4336        >>> from_("tbl").select("col1", "col2").sql()
4337        'SELECT col1, col2 FROM tbl'
4338
4339    Args:
4340        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4341            SELECT statement. If an Expression instance is passed, this is used as-is.
4342        dialect (str): the dialect used to parse the input expression (in the case that the
4343            input expression is a SQL string).
4344        **opts: other options to use to parse the input expressions (again, in the case
4345            that the input expression is a SQL string).
4346
4347    Returns:
4348        Select: the syntax tree for the SELECT statement.
4349    """
4350    return Select().from_(*expressions, dialect=dialect, **opts)
4351
4352
4353def update(
4354    table: str | Table,
4355    properties: dict,
4356    where: t.Optional[ExpOrStr] = None,
4357    from_: t.Optional[ExpOrStr] = None,
4358    dialect: DialectType = None,
4359    **opts,
4360) -> Update:
4361    """
4362    Creates an update statement.
4363
4364    Example:
4365        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4366        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4367
4368    Args:
4369        *properties: dictionary of properties to set which are
4370            auto converted to sql objects eg None -> NULL
4371        where: sql conditional parsed into a WHERE statement
4372        from_: sql statement parsed into a FROM statement
4373        dialect: the dialect used to parse the input expressions.
4374        **opts: other options to use to parse the input expressions.
4375
4376    Returns:
4377        Update: the syntax tree for the UPDATE statement.
4378    """
4379    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4380    update_expr.set(
4381        "expressions",
4382        [
4383            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4384            for k, v in properties.items()
4385        ],
4386    )
4387    if from_:
4388        update_expr.set(
4389            "from",
4390            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4391        )
4392    if isinstance(where, Condition):
4393        where = Where(this=where)
4394    if where:
4395        update_expr.set(
4396            "where",
4397            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4398        )
4399    return update_expr
4400
4401
4402def delete(
4403    table: ExpOrStr,
4404    where: t.Optional[ExpOrStr] = None,
4405    returning: t.Optional[ExpOrStr] = None,
4406    dialect: DialectType = None,
4407    **opts,
4408) -> Delete:
4409    """
4410    Builds a delete statement.
4411
4412    Example:
4413        >>> delete("my_table", where="id > 1").sql()
4414        'DELETE FROM my_table WHERE id > 1'
4415
4416    Args:
4417        where: sql conditional parsed into a WHERE statement
4418        returning: sql conditional parsed into a RETURNING statement
4419        dialect: the dialect used to parse the input expressions.
4420        **opts: other options to use to parse the input expressions.
4421
4422    Returns:
4423        Delete: the syntax tree for the DELETE statement.
4424    """
4425    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
4426    if where:
4427        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
4428    if returning:
4429        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
4430    return delete_expr
4431
4432
4433def condition(expression, dialect=None, **opts) -> Condition:
4434    """
4435    Initialize a logical condition expression.
4436
4437    Example:
4438        >>> condition("x=1").sql()
4439        'x = 1'
4440
4441        This is helpful for composing larger logical syntax trees:
4442        >>> where = condition("x=1")
4443        >>> where = where.and_("y=1")
4444        >>> Select().from_("tbl").select("*").where(where).sql()
4445        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4446
4447    Args:
4448        *expression (str | Expression): the SQL code string to parse.
4449            If an Expression instance is passed, this is used as-is.
4450        dialect (str): the dialect used to parse the input expression (in the case that the
4451            input expression is a SQL string).
4452        **opts: other options to use to parse the input expressions (again, in the case
4453            that the input expression is a SQL string).
4454
4455    Returns:
4456        Condition: the expression
4457    """
4458    return maybe_parse(  # type: ignore
4459        expression,
4460        into=Condition,
4461        dialect=dialect,
4462        **opts,
4463    )
4464
4465
4466def and_(*expressions, dialect=None, **opts) -> And:
4467    """
4468    Combine multiple conditions with an AND logical operator.
4469
4470    Example:
4471        >>> and_("x=1", and_("y=1", "z=1")).sql()
4472        'x = 1 AND (y = 1 AND z = 1)'
4473
4474    Args:
4475        *expressions (str | Expression): the SQL code strings to parse.
4476            If an Expression instance is passed, this is used as-is.
4477        dialect (str): the dialect used to parse the input expression.
4478        **opts: other options to use to parse the input expressions.
4479
4480    Returns:
4481        And: the new condition
4482    """
4483    return _combine(expressions, And, dialect, **opts)
4484
4485
4486def or_(*expressions, dialect=None, **opts) -> Or:
4487    """
4488    Combine multiple conditions with an OR logical operator.
4489
4490    Example:
4491        >>> or_("x=1", or_("y=1", "z=1")).sql()
4492        'x = 1 OR (y = 1 OR z = 1)'
4493
4494    Args:
4495        *expressions (str | Expression): the SQL code strings to parse.
4496            If an Expression instance is passed, this is used as-is.
4497        dialect (str): the dialect used to parse the input expression.
4498        **opts: other options to use to parse the input expressions.
4499
4500    Returns:
4501        Or: the new condition
4502    """
4503    return _combine(expressions, Or, dialect, **opts)
4504
4505
4506def not_(expression, dialect=None, **opts) -> Not:
4507    """
4508    Wrap a condition with a NOT operator.
4509
4510    Example:
4511        >>> not_("this_suit='black'").sql()
4512        "NOT this_suit = 'black'"
4513
4514    Args:
4515        expression (str | Expression): the SQL code strings to parse.
4516            If an Expression instance is passed, this is used as-is.
4517        dialect (str): the dialect used to parse the input expression.
4518        **opts: other options to use to parse the input expressions.
4519
4520    Returns:
4521        Not: the new condition
4522    """
4523    this = condition(
4524        expression,
4525        dialect=dialect,
4526        **opts,
4527    )
4528    return Not(this=_wrap_operator(this))
4529
4530
4531def paren(expression) -> Paren:
4532    return Paren(this=expression)
4533
4534
4535SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4536
4537
4538@t.overload
4539def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4540    ...
4541
4542
4543@t.overload
4544def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4545    ...
4546
4547
4548def to_identifier(name, quoted=None):
4549    """Builds an identifier.
4550
4551    Args:
4552        name: The name to turn into an identifier.
4553        quoted: Whether or not force quote the identifier.
4554
4555    Returns:
4556        The identifier ast node.
4557    """
4558
4559    if name is None:
4560        return None
4561
4562    if isinstance(name, Identifier):
4563        identifier = name
4564    elif isinstance(name, str):
4565        identifier = Identifier(
4566            this=name,
4567            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
4568        )
4569    else:
4570        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4571    return identifier
4572
4573
4574INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4575
4576
4577def to_interval(interval: str | Literal) -> Interval:
4578    """Builds an interval expression from a string like '1 day' or '5 months'."""
4579    if isinstance(interval, Literal):
4580        if not interval.is_string:
4581            raise ValueError("Invalid interval string.")
4582
4583        interval = interval.this
4584
4585    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4586
4587    if not interval_parts:
4588        raise ValueError("Invalid interval string.")
4589
4590    return Interval(
4591        this=Literal.string(interval_parts.group(1)),
4592        unit=Var(this=interval_parts.group(2)),
4593    )
4594
4595
4596@t.overload
4597def to_table(sql_path: str | Table, **kwargs) -> Table:
4598    ...
4599
4600
4601@t.overload
4602def to_table(sql_path: None, **kwargs) -> None:
4603    ...
4604
4605
4606def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4607    """
4608    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4609    If a table is passed in then that table is returned.
4610
4611    Args:
4612        sql_path: a `[catalog].[schema].[table]` string.
4613
4614    Returns:
4615        A table expression.
4616    """
4617    if sql_path is None or isinstance(sql_path, Table):
4618        return sql_path
4619    if not isinstance(sql_path, str):
4620        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4621
4622    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4623    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4624
4625
4626def to_column(sql_path: str | Column, **kwargs) -> Column:
4627    """
4628    Create a column from a `[table].[column]` sql path. Schema is optional.
4629
4630    If a column is passed in then that column is returned.
4631
4632    Args:
4633        sql_path: `[table].[column]` string
4634    Returns:
4635        Table: A column expression
4636    """
4637    if sql_path is None or isinstance(sql_path, Column):
4638        return sql_path
4639    if not isinstance(sql_path, str):
4640        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4641    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
4642
4643
4644def alias_(
4645    expression: ExpOrStr,
4646    alias: str | Identifier,
4647    table: bool | t.Sequence[str | Identifier] = False,
4648    quoted: t.Optional[bool] = None,
4649    dialect: DialectType = None,
4650    **opts,
4651):
4652    """Create an Alias expression.
4653
4654    Example:
4655        >>> alias_('foo', 'bar').sql()
4656        'foo AS bar'
4657
4658        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4659        '(SELECT 1, 2) AS bar(a, b)'
4660
4661    Args:
4662        expression: the SQL code strings to parse.
4663            If an Expression instance is passed, this is used as-is.
4664        alias: the alias name to use. If the name has
4665            special characters it is quoted.
4666        table: Whether or not to create a table alias, can also be a list of columns.
4667        quoted: whether or not to quote the alias
4668        dialect: the dialect used to parse the input expression.
4669        **opts: other options to use to parse the input expressions.
4670
4671    Returns:
4672        Alias: the aliased expression
4673    """
4674    exp = maybe_parse(expression, dialect=dialect, **opts)
4675    alias = to_identifier(alias, quoted=quoted)
4676
4677    if table:
4678        table_alias = TableAlias(this=alias)
4679        exp.set("alias", table_alias)
4680
4681        if not isinstance(table, bool):
4682            for column in table:
4683                table_alias.append("columns", to_identifier(column, quoted=quoted))
4684
4685        return exp
4686
4687    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4688    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4689    # for the complete Window expression.
4690    #
4691    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4692
4693    if "alias" in exp.arg_types and not isinstance(exp, Window):
4694        exp = exp.copy()
4695        exp.set("alias", alias)
4696        return exp
4697    return Alias(this=exp, alias=alias)
4698
4699
4700def subquery(expression, alias=None, dialect=None, **opts):
4701    """
4702    Build a subquery expression.
4703
4704    Example:
4705        >>> subquery('select x from tbl', 'bar').select('x').sql()
4706        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4707
4708    Args:
4709        expression (str | Expression): the SQL code strings to parse.
4710            If an Expression instance is passed, this is used as-is.
4711        alias (str | Expression): the alias name to use.
4712        dialect (str): the dialect used to parse the input expression.
4713        **opts: other options to use to parse the input expressions.
4714
4715    Returns:
4716        Select: a new select with the subquery expression included
4717    """
4718
4719    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4720    return Select().from_(expression, dialect=dialect, **opts)
4721
4722
4723def column(
4724    col: str | Identifier,
4725    table: t.Optional[str | Identifier] = None,
4726    db: t.Optional[str | Identifier] = None,
4727    catalog: t.Optional[str | Identifier] = None,
4728    quoted: t.Optional[bool] = None,
4729) -> Column:
4730    """
4731    Build a Column.
4732
4733    Args:
4734        col: column name
4735        table: table name
4736        db: db name
4737        catalog: catalog name
4738        quoted: whether or not to force quote each part
4739    Returns:
4740        Column: column instance
4741    """
4742    return Column(
4743        this=to_identifier(col, quoted=quoted),
4744        table=to_identifier(table, quoted=quoted),
4745        db=to_identifier(db, quoted=quoted),
4746        catalog=to_identifier(catalog, quoted=quoted),
4747    )
4748
4749
4750def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
4751    """Cast an expression to a data type.
4752
4753    Example:
4754        >>> cast('x + 1', 'int').sql()
4755        'CAST(x + 1 AS INT)'
4756
4757    Args:
4758        expression: The expression to cast.
4759        to: The datatype to cast to.
4760
4761    Returns:
4762        A cast node.
4763    """
4764    expression = maybe_parse(expression, **opts)
4765    return Cast(this=expression, to=DataType.build(to, **opts))
4766
4767
4768def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4769    """Build a Table.
4770
4771    Args:
4772        table (str | Expression): column name
4773        db (str | Expression): db name
4774        catalog (str | Expression): catalog name
4775
4776    Returns:
4777        Table: table instance
4778    """
4779    return Table(
4780        this=to_identifier(table, quoted=quoted),
4781        db=to_identifier(db, quoted=quoted),
4782        catalog=to_identifier(catalog, quoted=quoted),
4783        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4784    )
4785
4786
4787def values(
4788    values: t.Iterable[t.Tuple[t.Any, ...]],
4789    alias: t.Optional[str] = None,
4790    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4791) -> Values:
4792    """Build VALUES statement.
4793
4794    Example:
4795        >>> values([(1, '2')]).sql()
4796        "VALUES (1, '2')"
4797
4798    Args:
4799        values: values statements that will be converted to SQL
4800        alias: optional alias
4801        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4802         If either are provided then an alias is also required.
4803         If a dictionary is provided then the first column of the values will be casted to the expected type
4804         in order to help with type inference.
4805
4806    Returns:
4807        Values: the Values expression object
4808    """
4809    if columns and not alias:
4810        raise ValueError("Alias is required when providing columns")
4811    table_alias = (
4812        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4813        if columns
4814        else TableAlias(this=to_identifier(alias) if alias else None)
4815    )
4816    expressions = [convert(tup) for tup in values]
4817    if columns and isinstance(columns, dict):
4818        types = list(columns.values())
4819        expressions[0].set(
4820            "expressions",
4821            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4822        )
4823    return Values(
4824        expressions=expressions,
4825        alias=table_alias,
4826    )
4827
4828
4829def var(name: t.Optional[ExpOrStr]) -> Var:
4830    """Build a SQL variable.
4831
4832    Example:
4833        >>> repr(var('x'))
4834        '(VAR this: x)'
4835
4836        >>> repr(var(column('x', table='y')))
4837        '(VAR this: x)'
4838
4839    Args:
4840        name: The name of the var or an expression who's name will become the var.
4841
4842    Returns:
4843        The new variable node.
4844    """
4845    if not name:
4846        raise ValueError("Cannot convert empty name into var.")
4847
4848    if isinstance(name, Expression):
4849        name = name.name
4850    return Var(this=name)
4851
4852
4853def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4854    """Build ALTER TABLE... RENAME... expression
4855
4856    Args:
4857        old_name: The old name of the table
4858        new_name: The new name of the table
4859
4860    Returns:
4861        Alter table expression
4862    """
4863    old_table = to_table(old_name)
4864    new_table = to_table(new_name)
4865    return AlterTable(
4866        this=old_table,
4867        actions=[
4868            RenameTable(this=new_table),
4869        ],
4870    )
4871
4872
4873def convert(value) -> Expression:
4874    """Convert a python value into an expression object.
4875
4876    Raises an error if a conversion is not possible.
4877
4878    Args:
4879        value (Any): a python object
4880
4881    Returns:
4882        Expression: the equivalent expression object
4883    """
4884    if isinstance(value, Expression):
4885        return value
4886    if value is None:
4887        return NULL
4888    if isinstance(value, bool):
4889        return Boolean(this=value)
4890    if isinstance(value, str):
4891        return Literal.string(value)
4892    if isinstance(value, float) and math.isnan(value):
4893        return NULL
4894    if isinstance(value, numbers.Number):
4895        return Literal.number(value)
4896    if isinstance(value, tuple):
4897        return Tuple(expressions=[convert(v) for v in value])
4898    if isinstance(value, list):
4899        return Array(expressions=[convert(v) for v in value])
4900    if isinstance(value, dict):
4901        return Map(
4902            keys=[convert(k) for k in value],
4903            values=[convert(v) for v in value.values()],
4904        )
4905    if isinstance(value, datetime.datetime):
4906        datetime_literal = Literal.string(
4907            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4908        )
4909        return TimeStrToTime(this=datetime_literal)
4910    if isinstance(value, datetime.date):
4911        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4912        return DateStrToDate(this=date_literal)
4913    raise ValueError(f"Cannot convert {value}")
4914
4915
4916def replace_children(expression, fun, *args, **kwargs):
4917    """
4918    Replace children of an expression with the result of a lambda fun(child) -> exp.
4919    """
4920    for k, v in expression.args.items():
4921        is_list_arg = type(v) is list
4922
4923        child_nodes = v if is_list_arg else [v]
4924        new_child_nodes = []
4925
4926        for cn in child_nodes:
4927            if isinstance(cn, Expression):
4928                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
4929                    new_child_nodes.append(child_node)
4930                    child_node.parent = expression
4931                    child_node.arg_key = k
4932            else:
4933                new_child_nodes.append(cn)
4934
4935        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4936
4937
4938def column_table_names(expression):
4939    """
4940    Return all table names referenced through columns in an expression.
4941
4942    Example:
4943        >>> import sqlglot
4944        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4945        ['c', 'a']
4946
4947    Args:
4948        expression (sqlglot.Expression): expression to find table names
4949
4950    Returns:
4951        list: A list of unique names
4952    """
4953    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4954
4955
4956def table_name(table) -> str:
4957    """Get the full name of a table as a string.
4958
4959    Args:
4960        table (exp.Table | str): table expression node or string.
4961
4962    Examples:
4963        >>> from sqlglot import exp, parse_one
4964        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4965        'a.b.c'
4966
4967    Returns:
4968        The table name.
4969    """
4970
4971    table = maybe_parse(table, into=Table)
4972
4973    if not table:
4974        raise ValueError(f"Cannot parse {table}")
4975
4976    return ".".join(
4977        part
4978        for part in (
4979            table.text("catalog"),
4980            table.text("db"),
4981            table.name,
4982        )
4983        if part
4984    )
4985
4986
4987def replace_tables(expression, mapping):
4988    """Replace all tables in expression according to the mapping.
4989
4990    Args:
4991        expression (sqlglot.Expression): expression node to be transformed and replaced.
4992        mapping (Dict[str, str]): mapping of table names.
4993
4994    Examples:
4995        >>> from sqlglot import exp, parse_one
4996        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4997        'SELECT * FROM c'
4998
4999    Returns:
5000        The mapped expression.
5001    """
5002
5003    def _replace_tables(node):
5004        if isinstance(node, Table):
5005            new_name = mapping.get(table_name(node))
5006            if new_name:
5007                return to_table(
5008                    new_name,
5009                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
5010                )
5011        return node
5012
5013    return expression.transform(_replace_tables)
5014
5015
5016def replace_placeholders(expression, *args, **kwargs):
5017    """Replace placeholders in an expression.
5018
5019    Args:
5020        expression (sqlglot.Expression): expression node to be transformed and replaced.
5021        args: positional names that will substitute unnamed placeholders in the given order.
5022        kwargs: keyword arguments that will substitute named placeholders.
5023
5024    Examples:
5025        >>> from sqlglot import exp, parse_one
5026        >>> replace_placeholders(
5027        ...     parse_one("select * from :tbl where ? = ?"),
5028        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
5029        ... ).sql()
5030        "SELECT * FROM foo WHERE str_col = 'b'"
5031
5032    Returns:
5033        The mapped expression.
5034    """
5035
5036    def _replace_placeholders(node, args, **kwargs):
5037        if isinstance(node, Placeholder):
5038            if node.name:
5039                new_name = kwargs.get(node.name)
5040                if new_name:
5041                    return convert(new_name)
5042            else:
5043                try:
5044                    return convert(next(args))
5045                except StopIteration:
5046                    pass
5047        return node
5048
5049    return expression.transform(_replace_placeholders, iter(args), **kwargs)
5050
5051
5052def expand(
5053    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
5054) -> Expression:
5055    """Transforms an expression by expanding all referenced sources into subqueries.
5056
5057    Examples:
5058        >>> from sqlglot import parse_one
5059        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
5060        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
5061
5062        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
5063        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
5064
5065    Args:
5066        expression: The expression to expand.
5067        sources: A dictionary of name to Subqueryables.
5068        copy: Whether or not to copy the expression during transformation. Defaults to True.
5069
5070    Returns:
5071        The transformed expression.
5072    """
5073
5074    def _expand(node: Expression):
5075        if isinstance(node, Table):
5076            name = table_name(node)
5077            source = sources.get(name)
5078            if source:
5079                subquery = source.subquery(node.alias or name)
5080                subquery.comments = [f"source: {name}"]
5081                return subquery.transform(_expand, copy=False)
5082        return node
5083
5084    return expression.transform(_expand, copy=copy)
5085
5086
5087def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
5088    """
5089    Returns a Func expression.
5090
5091    Examples:
5092        >>> func("abs", 5).sql()
5093        'ABS(5)'
5094
5095        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
5096        'CAST(5 AS DOUBLE)'
5097
5098    Args:
5099        name: the name of the function to build.
5100        args: the args used to instantiate the function of interest.
5101        dialect: the source dialect.
5102        kwargs: the kwargs used to instantiate the function of interest.
5103
5104    Note:
5105        The arguments `args` and `kwargs` are mutually exclusive.
5106
5107    Returns:
5108        An instance of the function of interest, or an anonymous function, if `name` doesn't
5109        correspond to an existing `sqlglot.expressions.Func` class.
5110    """
5111    if args and kwargs:
5112        raise ValueError("Can't use both args and kwargs to instantiate a function.")
5113
5114    from sqlglot.dialects.dialect import Dialect
5115
5116    converted = [convert(arg) for arg in args]
5117    kwargs = {key: convert(value) for key, value in kwargs.items()}
5118
5119    parser = Dialect.get_or_raise(dialect)().parser()
5120    from_args_list = parser.FUNCTIONS.get(name.upper())
5121
5122    if from_args_list:
5123        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
5124    else:
5125        kwargs = kwargs or {"expressions": converted}
5126        function = Anonymous(this=name, **kwargs)
5127
5128    for error_message in function.error_messages(converted):
5129        raise ValueError(error_message)
5130
5131    return function
5132
5133
5134def true():
5135    """
5136    Returns a true Boolean expression.
5137    """
5138    return Boolean(this=True)
5139
5140
5141def false():
5142    """
5143    Returns a false Boolean expression.
5144    """
5145    return Boolean(this=False)
5146
5147
5148def null():
5149    """
5150    Returns a Null expression.
5151    """
5152    return Null()
5153
5154
5155# TODO: deprecate this
5156TRUE = Boolean(this=True)
5157FALSE = Boolean(this=False)
5158NULL = Null()